Beispiel #1
0
def autocomplete(request):
    objName = dictObj[request.matchdict['obj']]
    session = request.dbsession
    criteria = request.params['term']
    prop = asInt(request.matchdict['prop'])
    try:
        NameValReturn = request.matchdict['valReturn']
    except:
        NameValReturn = None

    if isinstance(prop, int):
        table = Base.metadata.tables[objName + 'DynPropValuesNow']
        query = select([table.c['ValueString'].label('label'),
                        table.c['ValueString'].label('value')]
                       ).distinct(table.c['ValueString']
                                  ).where(table.c['FK_' + objName + 'DynProp'] == prop)
        query = query.where(table.c['ValueString'].like('%' + criteria + '%')
                            ).order_by(asc(table.c['ValueString']))
    else:
        if NameValReturn is None:
            NameValReturn = prop
        table = Base.metadata.tables[objName]
        query = select([table.c[NameValReturn].label('value'),
                        table.c[prop].label('label')]
                       ).distinct(table.c[prop])
        query = query.where(table.c[prop].like(
            '%' + criteria + '%')).order_by(asc(table.c[prop]))

    return [dict(row) for row in session.execute(query).fetchall()]
def showCollections():
    movies = session.query(Movie).order_by(asc(Movie.name))
    collections = session.query(Collection).order_by(asc(Collection.name))
    if 'username' not in login_session:
        return render_template('publicmovies.html', movies = movies, collections = collections)
    else:
        return render_template('movies.html', movies = movies, collections = collections)
Beispiel #3
0
def index():
    if app.mixer.disp_count == 1:
        disp = db.session.query(Dispenser) \
                          .filter(Dispenser.id == 1) \
                          .one()
        return render_template("shotbot", booze=disp.booze.name, title="ShotBot")

    can_make = app.mixer.get_available_drink_list()
    can_make_dict = {}
    for drink in can_make:
        can_make_dict[drink] = 1

    top_drinks = db.session.query(Drink) \
                        .join(DrinkName) \
                        .filter(Drink.name_id == DrinkName.id)  \
                        .filter(Drink.popular == 1)  \
                        .filter(Drink.available == 1)  \
                        .order_by(asc(func.lower(DrinkName.name))).all() 
    top_drinks = filter_drink_list(can_make_dict, top_drinks)
    process_ingredients(top_drinks)

    other_drinks = db.session.query(Drink) \
                        .join(DrinkName) \
                        .filter(Drink.name_id == DrinkName.id)  \
                        .filter(Drink.popular == 0)  \
                        .filter(Drink.available == 1)  \
                        .order_by(asc(func.lower(DrinkName.name))).all() 
    other_drinks = filter_drink_list(can_make_dict, other_drinks)
    process_ingredients(other_drinks)
    user = root_menu.index_layout()
    return render_template("index", 
                           top_drinks=top_drinks, 
                           other_drinks=other_drinks,
                           title="Bartendro", user=user)
def addItem():
    """
    Adds a new Catalog item to a specific category
    """

    if 'username' not in login_session:
        logged_in = False
        return redirect('/login')
    else:
        logged_in = True

    if request.method == 'POST':
        if request.form['name']:
            new_item = Item(name=request.form['name'], description=request.form['description'], category_id=request.form['category'])
            session.add(new_item)
            session.commit()
            return redirect(url_for('catalog'))
        else:
            categories = session.query(Category).order_by(asc(Category.name))
            return render_template('add-item.html', categories=categories
                    , logged_in=logged_in)
    else:
        categories = session.query(Category).order_by(asc(Category.name))
        return render_template('add-item.html', categories=categories
                , logged_in=logged_in)
Beispiel #5
0
    def getTop(num=100, highlight_funcs=[], remove_funcs=[]):
        """
            Get the top [amount] filtered URLS for a case. This has been optimised as one 
            query with a subquery. 
        """        

        filter = getFilter(highlight_funcs, remove_funcs)
        
        url2 = aliased(URL)
        subq = session.query(url2.domain, func.count(url2.domain).label('domain_count'))\
                .join(filter).filter(url2.domain != None)\
                .group_by(url2.domain).order_by(desc(func.count(url2.domain)))

        if num != "all":
            subq = subq.limit(num)           
        subq = subq.subquery()

        q = session.query(URL.netloc, func.count(1), subq.c.domain, subq.c.domain_count)\
            .join((subq, URL.domain == subq.c.domain))\
            .join(filter)\
            .group_by(URL.netloc)\
            .order_by(desc(subq.c.domain_count), asc(subq.c.domain), asc(URL.netloc))

        domains = []
        for netloc, netloc_count, domain, domain_count in q:
            if len(domains) == 0 or domains[-1][0] != domain:
                domains.append((domain, domain_count, []))
                
            domains[-1][2].append((netloc, netloc_count))
          
        return domains 
Beispiel #6
0
def showCatalog():
  '''Method to display contents of the home page of SnapShare application!'''
  print "in show catalog"
  albums = session.query(Album).order_by(asc(Album.name))
  photos = session.query(Photo).order_by(asc(Photo.year))
  #return render_template('catalog.html', albums=albums, photos = photos)
  return jsonify(Albums =[a.serialize for a in albums], Photos = [p.serialize for p in photos])
Beispiel #7
0
def _subnet_find_ordered_by_used_ips(context, net_id, lock_subnets=True,
                                     order="asc", unused=False, **filters):
    count = sql_func.count(models.IPAddress.address).label("count")
    size = (models.Subnet.last_ip - models.Subnet.first_ip)
    query = context.session.query(models.Subnet, count)
    if lock_subnets:
        query = query.with_lockmode("update")
    query = query.filter_by(do_not_use=False)
    query = query.outerjoin(models.Subnet.generated_ips)
    query = query.group_by(models.Subnet.id)

    query = query.order_by(asc(models.Subnet.ip_version))

    if unused:  # find unused subnets
        query = query.having(count == 0)
    else:  # otherwise, order used subnets
        if order == "desc":
            query = query.order_by(desc(size - count))
        else:  # default acending
            query = query.order_by(asc(size - count))

    query = query.filter(models.Subnet.network_id == net_id)
    if "ip_version" in filters:
        query = query.filter(models.Subnet.ip_version == filters["ip_version"])
    if "segment_id" in filters and filters["segment_id"]:
        query = query.filter(models.Subnet.segment_id == filters["segment_id"])
    query = query.filter(models.Subnet.next_auto_assign_ip != -1)

    if "subnet_id" in filters and filters["subnet_id"]:
        query = query.filter(models.Subnet.id.in_(filters["subnet_id"]))
    return query
Beispiel #8
0
def editCatalogItem(item_name):
    if 'username' not in login_session:
        return redirect('/login')
    editedItem = session.query(CatalogItem).filter_by(name=item_name).one()
    if request.method == 'POST':
        #We have to be carefull about having 2 Items with the same name
        #First we check if the name is the same.
        if request.form['name'] != editedItem.name:
            #If name changes, we look to see if it already existes
            if not session.query(CatalogItem).filter_by(name=request.form['name']).count():
                editedItem.name = request.form['name']
            else:
                flash('An item with the name %s already exist!' % (request.form['name']))
                categories = session.query(Category).order_by(asc(Category.name))
                return render_template('editcatalogitem.html', item=editedItem,
                                       categories=categories,
                                       login_session=login_session)
        if request.form['description']:
            editedItem.description = request.form['description']
        if request.form['category_id']:
            editedItem.category_id = request.form['category_id']
        session.add(editedItem)
        session.commit()
        flash('Menu Item Successfully Edited')
        return redirect(url_for('showHome'))
    else:
        categories = session.query(Category).order_by(asc(Category.name))
        return render_template('editcatalogitem.html', item=editedItem,
                               categories=categories,
                               login_session=login_session)
Beispiel #9
0
    def _changeOrderBy(self, widget, fieldsString):
        """ Changes results order
        RITOCCO fatto per SA 0.6.3 ...ancora da approfondire
        """
        if sqlalchemy.__version__ > "0.6.0":
            if (self.orderBy is not None) == (fieldsString[1] is not None):
                if self.flag == False:
                    self.orderBy = asc(fieldsString[1])
                    self.flag = True
                else:
                    self.orderBy = desc(fieldsString[1])
                    self.flag = False
            else:
                self.orderBy = fieldsString[1]

            if fieldsString[0] is not None:
                self.join = fieldsString[0]
        else:
            if self.orderBy == fieldsString[1]:
                if self.flag == False:
                    self.orderBy = asc(fieldsString[1])
                    self.flag = True
                else:
                    self.orderBy = desc(fieldsString[1])
                    self.flag = False
            else:
                self.orderBy = fieldsString[1]
            if fieldsString[0]:
                self.join = fieldsString[0]
        self.refresh()
Beispiel #10
0
 def parse_order_by(self, order_by_string, default=None):
     """Return an ORM compatible order_by using the given string"""
     # TODO: generalize into class
     # TODO: general (enough) columns
     if order_by_string in ('create_time', 'create_time-dsc'):
         return desc(self.model_class.create_time)
     if order_by_string == 'create_time-asc':
         return asc(self.model_class.create_time)
     if order_by_string in ('update_time', 'update_time-dsc'):
         return desc(self.model_class.update_time)
     if order_by_string == 'update_time-asc':
         return asc(self.model_class.update_time)
     if order_by_string in ('name', 'name-asc'):
         return asc(self.model_class.name)
     if order_by_string == 'name-dsc':
         return desc(self.model_class.name)
     # TODO: history columns
     if order_by_string in ('size', 'size-dsc'):
         return desc(self.model_class.disk_size)
     if order_by_string == 'size-asc':
         return asc(self.model_class.disk_size)
     # TODO: add functional/non-orm orders (such as rating)
     if default:
         return self.parse_order_by(default)
     raise glx_exceptions.RequestParameterInvalidException('Unkown order_by', order_by=order_by_string,
         available=['create_time', 'update_time', 'name', 'size'])
Beispiel #11
0
def user_permissions(session, user):

    user_groups = get_all_groups_by_user(session, user)
    non_np = [grp.name for grp, role in user_groups if role != GROUP_EDGE_ROLES.index("np-owner")]

    now = datetime.utcnow()
    permissions = session.query(
        Permission.name,
        PermissionMap.argument,
        PermissionMap.granted_on,
        Group,
    ).filter(
        PermissionMap.permission_id == Permission.id,
        PermissionMap.group_id == Group.id,
        GroupEdge.group_id == Group.id,
        GroupEdge.active == True,
        user.enabled == True,
        Group.enabled == True,
        Group.name.in_(non_np),
        or_(
            GroupEdge.expiration > now,
            GroupEdge.expiration == None
        )
    ).order_by(
        asc("name"), asc("argument"), asc("groupname")
    ).all()

    return permissions
Beispiel #12
0
 def test_paginate_query_attribute_error(self):
     sqlalchemy.asc('user').AndReturn('asc')
     self.query.order_by('asc').AndReturn(self.query)
     self.mox.ReplayAll()
     self.assertRaises(exception.InvalidSortKey,
                       utils.paginate_query, self.query,
                       self.model, 5, ['user_id', 'non-existent key'])
Beispiel #13
0
 def test_paginate_query_value_error(self):
     sqlalchemy.asc('user').AndReturn('asc_1')
     self.query.order_by('asc_1').AndReturn(self.query)
     self.mox.ReplayAll()
     self.assertRaises(ValueError, utils.paginate_query,
                       self.query, self.model, 5, ['user_id', 'project_id'],
                       marker=self.marker, sort_dirs=['asc', 'mixed'])
Beispiel #14
0
    def prepare(self):
        conditions = []
        if self.host_id is not None:
            conditions.append(Attribute.host_id == self.host_id)
        if self.alarmed_only:
            conditions.append(EventState.internal_state != State.UP)
        attributes = DBSession.query(Attribute).join(Host, EventState).\
            filter(and_(*conditions)).\
            order_by(asc(Host.display_name), asc(Attribute.display_name))
        if attributes.count() == 0:
            flash('No Attributes Found', 'alert')
            self.map_groups = None
        else:
            for attribute in attributes:
                astate, state_desc = self.attribute_state(attribute)

                try:
                    atype = attribute.attribute_type.display_name
                except AttributeError:
                    atype = 'Unknown'
                att_fields = [('Host', attribute.host.display_name),
                              ('Type', atype),
                              ('Status', state_desc), ]
                for k, v in attribute.description_dict().items():
                    if v != '':
                        att_fields.append((k, v))
                self.add_item(attribute.host_id, attribute.host.display_name,
                              [('Address', attribute.host.mgmt_address)],
                              {'name': attribute.display_name,
                               'state': astate,
                               'url': url('/attributes/'+str(attribute.id)),
                               'fields': att_fields,
                               })
        super(AttributeMap, self).prepare()
def list_project_searches(project_external_id):
    project = get_project_by_id_or_404(project_external_id)

    page = get_valid_page_or_1()

    searches = DirectAwardSearch.query.filter(DirectAwardSearch.project_id == project.id)

    if 'latest-first' in request.args:
        if convert_to_boolean(request.args.get('latest-first')):
            searches = searches.order_by(desc(DirectAwardSearch.created_at), desc(DirectAwardSearch.id))
        else:
            searches = searches.order_by(asc(DirectAwardSearch.created_at), asc(DirectAwardSearch.id))
    else:
        searches = searches.order_by(asc(DirectAwardSearch.id))

    if convert_to_boolean(request.args.get('only-active', False)):
        searches = searches.filter(DirectAwardSearch.active == True)  # noqa

    pagination_params = request.args.to_dict()
    pagination_params['project_external_id'] = project.external_id

    return paginated_result_response(
        result_name="searches",
        results_query=searches,
        page=page,
        per_page=current_app.config['DM_API_PROJECTS_PAGE_SIZE'],
        endpoint='.list_project_searches',
        request_args=pagination_params,
    ), 200
Beispiel #16
0
 def prepare(self):
     conditions = []
     conditions.append(Host.show_host == True)  # noqa
     if self.zone_id is not None:
         conditions.append(Host.zone_id == self.zone_id)
     hosts = DBSession.query(Host).join(Zone).filter(
         and_(*conditions)).order_by(asc(Zone.display_name),
                                     asc(Host.display_name))
     if hosts.count() == 0:
         flash('No Hosts Found',  'alert')
         self.map_groups = None
     else:
         for host in hosts:
             vendor, device = host.snmp_type()
             hstate, state_desc = self.host_state(host)
             if self.alarmed_only and hstate == 'ok':
                 continue
             host_fields = [('Zone', host.zone.display_name),
                            ('Status', state_desc),
                            ('Vendor', vendor),
                            ('Device', device),
                            ('Address', host.mgmt_address)]
             self.add_item(host.zone_id, host.zone.display_name,
                           [],
                           {'name': host.display_name,
                            'state': hstate,
                            'url': url('/attributes/map/', {'h': host.id}),
                            'fields': host_fields,
                            })
     super(HostMap, self).prepare()
Beispiel #17
0
def user_permissions(session, user):

    # TODO: Make this walk the tree, so we can get a user's entire set of permissions.
    now = datetime.utcnow()
    permissions = session.query(
        Permission.name,
        PermissionMap.argument,
        PermissionMap.granted_on,
        Group,
    ).filter(
        PermissionMap.permission_id == Permission.id,
        PermissionMap.group_id == Group.id,
        GroupEdge.group_id == Group.id,
        GroupEdge.member_pk == user.id,
        GroupEdge.member_type == 0,
        GroupEdge.active == True,
        user.enabled == True,
        Group.enabled == True,
        or_(
            GroupEdge.expiration > now,
            GroupEdge.expiration == None
        )
    ).order_by(
        asc("name"), asc("argument"), asc("groupname")
    ).all()

    return permissions
Beispiel #18
0
def showGenres():
    songs = session.query(Song).order_by(asc(Song.name))
    genres = session.query(Genre).order_by(asc(Genre.name))
    if 'username' not in login_session:
        return render_template('publicsongs.html', songs = songs, genres = genres)
    else:
        return render_template('songs.html', songs = songs, genres = genres)
def showCategory(category_label):
    """Show Category.

    Displays the category and category items.

    Returns:
        The rendered Category view
    """
    try:
        category = _DBH.query(Category).filter_by(label=category_label).one()
    except NoResultFound:
        return redirect(url_for('category.showCategoryMasterDetail')), 404

    categories = (
        _DBH.query(Category)
        .order_by(asc(Category.label))
        .all()
        )
    items = (
        _DBH.query(Item)
        .filter_by(category_id=category.id)
        .order_by(asc(Item.label))
        .all()
        )

    if 'name' in _login_session:
        template_file = 'showCategory.html'
    else:
        template_file = 'showPublicCategory.html'

    return render_template(
        template_file, categories=categories, category=category, items=items)
Beispiel #20
0
 def test_paginate_query_attribute_error(self):
     sqlalchemy.asc("user").AndReturn("asc")
     self.query.order_by("asc").AndReturn(self.query)
     self.mox.ReplayAll()
     self.assertRaises(
         exception.InvalidSortKey, utils.paginate_query, self.query, self.model, 5, ["user_id", "non-existent key"]
     )
Beispiel #21
0
def get_tests_query(filter_object=None):

    # get the candidate tests to page through
    query = tst.CandidateTest.query

    # leave out alta tests
    query = query.join('candidate_profile','account')
    #query = query.filter(tst.Account.id!=352)
    query = query.filter(tst.Account.id==957)

    # leave out archived
    query = query.join('test','test','type')
    query = query.join('version')
    query = query.filter(and_(tst.AccountTest.archived==False, tst.Test.archived==False, tst.TestVersion.archived==False))

    # get only reading comprehension
    query = query.filter(tst.TestType.id==6)

    # ensure there are responses
    query = query.join('sections','items')
    query = query.filter(and_(tst.CandidateTestItem.response!=None, tst.CandidateTestItem.response!=''))

    # order by test version
    query = query.order_by(asc(tst.Test.id),asc(tst.TestVersion.id),asc(tst.CandidateTest.local_date))

    # eagerload our responses
    query = query.options(eagerload_all('sections.items'), eagerload('sections.items.item.type.has_response'))

    return query.distinct()
Beispiel #22
0
def showRestaurants():
    restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
    cuisine = session.query(Restaurant).order_by(asc(Restaurant.cuisine_cat))
    food = ['Asian', 'Italian', 'American', 'Greek', 'Vegetarian', 'Mexican', \
     'All']
    return render_template('restaurantlarge.html', restaurants=restaurants, \
        food=food)
Beispiel #23
0
 def get_monitor_data(cls, copr):
     subquery = (
         db.session.query(
             db.func.max(Build.id).label("max_build_id_for_chroot"),
             Build.package_id,
             BuildChroot.mock_chroot_id)
         .join(BuildChroot, and_(Build.id == BuildChroot.build_id))
         .filter(Build.copr_id == copr.id)
         .filter(BuildChroot.status != 2)
         .group_by(Build.package_id, BuildChroot.mock_chroot_id)
         .subquery("max_build_ids_for_a_chroot")
     )
     query = (
         db.session.query(Package, Build, BuildChroot, MockChroot)
         .join(subquery, and_(Package.id == subquery.c.package_id))
         .join(Build, and_(Build.id == subquery.c.max_build_id_for_chroot))
         .join(BuildChroot, and_(
             BuildChroot.mock_chroot_id == subquery.c.mock_chroot_id,
             BuildChroot.build_id == subquery.c.max_build_id_for_chroot
         ))
         .join(MockChroot, and_(MockChroot.id == subquery.c.mock_chroot_id))
         .order_by(
             asc(Package.name),
             asc(Package.id), # in case there are two packages with the same name
             asc(MockChroot.os_release),
             asc(MockChroot.os_version),
             asc(MockChroot.arch)
         )
     )
     return query.all()
    def build_mpd_string(base_url, video, last_requested_segment_id=None):
        segments = []
        if last_requested_segment_id is not None:
            segments = session \
                .query(VideoSegment) \
                .filter((VideoSegment.video_id == video.video_id) &
                        (VideoSegment.segment_id > last_requested_segment_id) &
                        (VideoSegment.status == 'OK')) \
                .order_by(asc(VideoSegment.segment_id)) \
                .all()
        else:
            segments = session \
                .query(VideoSegment) \
                .filter((VideoSegment.video_id == video.video_id) &
                        (VideoSegment.status == 'OK')) \
                .order_by(asc(VideoSegment.segment_id)) \
                .all()

        repr_list = [video.repr_1, video.repr_2, video.repr_3]

        new_last_segment_id = None
        if segments and len(segments) > 0:
            new_last_segment_id = segments[-1].segment_id

        return gen_mpd(base_url=base_url,
                       repr_list=repr_list,
                       segment_duration_millis=video.segment_duration,
                       segment_list=segments), new_last_segment_id
Beispiel #25
0
def editItem():
    # If not logged in redirect to login page, flash message
    if 'username' not in login_session:
        flash('You must be logged in to do that!')
        return redirect('/login')
    # Select and pass to template only items created by the current
    items = session.query(Item).filter_by(
        user_id=login_session['user_id']).order_by(asc(Item.name))
    categories = session.query(Category).order_by(asc(Category.name))
    if request.method == 'POST':
        editedItem = session.query(Item).filter_by(
            name=request.form['old_name']).one()
        if request.form['name']:
            editedItem.name = request.form['name']
        if request.form['description']:
            editedItem.description = request.form['description']
        if request.form['category']:
            newCategory = session.query(Category).filter_by(
                name=request.form['category']).one()
            editedItem.category_id = newCategory.id
        session.add(editedItem)
        session.commit()
        flash("Item edited!")
        return redirect(url_for('showCatalog'))
    else:
        return render_template('edititem.html',
                               items=items, categories=categories)
    def test_paginate_query_null(self):
        self.mox.StubOutWithMock(self.model.user_id, 'isnot')
        self.model.user_id.isnot(None).AndReturn('asc_null_1')
        sqlalchemy.desc('asc_null_1').AndReturn('asc_null_2')
        self.query.order_by('asc_null_2').AndReturn(self.query)

        sqlalchemy.asc(self.model.user_id).AndReturn('asc_1')
        self.query.order_by('asc_1').AndReturn(self.query)

        self.mox.StubOutWithMock(self.model.project_id, 'is_')
        self.model.project_id.is_(None).AndReturn('desc_null_1')
        sqlalchemy.desc('desc_null_1').AndReturn('desc_null_2')
        self.query.order_by('desc_null_2').AndReturn(self.query)

        sqlalchemy.desc(self.model.project_id).AndReturn('desc_1')
        self.query.order_by('desc_1').AndReturn(self.query)

        self.mox.StubOutWithMock(sqlalchemy.sql, 'and_')
        sqlalchemy.sql.and_(mock.ANY).AndReturn('some_crit')
        sqlalchemy.sql.and_(mock.ANY, mock.ANY).AndReturn('another_crit')
        self.mox.StubOutWithMock(sqlalchemy.sql, 'or_')
        sqlalchemy.sql.or_('some_crit', 'another_crit').AndReturn('some_f')
        self.query.filter('some_f').AndReturn(self.query)
        self.query.limit(5).AndReturn(self.query)
        self.mox.ReplayAll()
        utils.paginate_query(self.query, self.model, 5,
                             ['user_id', 'project_id'],
                             marker=self.marker,
                             sort_dirs=['asc-nullslast', 'desc-nullsfirst'])
Beispiel #27
0
    def retrieve(self):
        objName = self.parent.item.model.__tablename__
        criteria = self.request.params['term']
        prop = self.attribute

        if self.integers(prop):
            table = Base.metadata.tables[objName + 'DynPropValuesNow']
            query = select([table.c['ValueString'].label('label'),
                            table.c['ValueString'].label('value')]
                           ).distinct(table.c['ValueString']
                                      ).where(table.c['FK_' + objName + 'DynProp'] == prop)
            query = query.where(table.c['ValueString'].like('%' + criteria + '%')
                                ).order_by(asc(table.c['ValueString']))
        else:
            NameValReturn = prop
            if self.targetValue:
                NameValReturn = self.targetValue

            table = Base.metadata.tables[objName]
            query = select([table.c[NameValReturn].label('value'),
                            table.c[prop].label('label')]
                           ).distinct(table.c[prop])
            query = query.where(table.c[prop].like(
                '%' + criteria + '%')).order_by(asc(table.c[prop]))

        return [dict(row) for row in self.session.execute(query).fetchall()]
Beispiel #28
0
    def distance(self,id):
        c.distance='''{
label: 'Distance(km)',
data:['''
        q=model.Session.query(model.trackpoint).filter(model.trackpoint.id==id)
        c.infomarker = q.one()
        q = model.Session.query(model.track).filter(model.track.id==c.infomarker.track_id)
        c.track = q.one()
        q=model.Session.query(model.trackpoint).filter(model.trackpoint.track_id==c.track.id)
        trackpoint=q.order_by(asc(model.trackpoint.timestamp)).first()
        oldlat=float(trackpoint.latitude)
        oldlon=float(trackpoint.longitude)
        distance=0
        trackpoints=q.order_by(asc(model.trackpoint.timestamp)).all()
        for trackpoint in trackpoints:
            newlat=float(trackpoint.latitude)
            newlon=float(trackpoint.longitude)
            if oldlat==newlat and oldlon==newlon:
                pass
            else:
                try:
                    distance=distance+h.distance_on_unit_sphere(oldlat, oldlon, newlat, newlon)*6373
                except typeError:
                    return oldlat,oldlon,newlat,newlon
            oldlat=newlat
            oldlon=newlon
            timestamp=trackpoint.timestamp
            c.distance=c.distance+'''['''+str(calendar.timegm(timestamp.timetuple()) * 1000)+''', '''+str(distance)+'''], '''
        c.distance=c.distance+''']}'''
        return c.distance
Beispiel #29
0
def statistics(mode=1, **kw):
    order = kw.get("order")
    sort = kw.get("sort")

    if mode == 1:
        query = db.session.query(Price)
    else:
        query = Price.query

    if order:
        query = query.filter_by(order=order)

    if sort:
        if sort == "ID":
            query = query.order_by(desc(Price.id))
        elif sort == "TOTAL-DESC":
            query = query.order_by(desc(Price.total))
        elif sort == "TOTAL-ASC":
            query = query.order_by(asc(Price.total))
        elif sort == "DATE-DESC":
            query = query.order_by(desc(Price.reg_date))
        elif sort == "DATE-ASC":
            query = query.order_by(asc(Price.reg_date))
        else:
            query = query.order_by(Price.id)
    else:
        query = query.order_by(desc(Price.reg_date))

    total = query.count()
    items = [
        (ob.id, ob.reg_date, ob.documentID, ob.documentDate, ob.priceTypeID, ob.currency, ob.total, ob.get_price())
        for ob in query.all()
    ]

    return (total, order and order.id, items, query)
Beispiel #30
0
 def list(self, **kwargs):
     """Get a list of valid projects"""
     try:
         limit = kwargs['limit'] if 'limit' in kwargs else 10
         page = kwargs['page'] if 'page' in kwargs else 0
         filters = self.get_filters(**kwargs)
         # In case of requiring languages, a LEFT JOIN must be generated
         if 'lang' in kwargs and kwargs['lang'] is not None:
             ret = []
             for u in ProjectLang.get_query(kwargs['lang']) \
                                 .filter(*filters) \
                                 .order_by(asc(self.created)) \
                                 .offset(page * limit) \
                                 .limit(limit):
                 ret.append(ProjectLang.get_translated_object(
                     u._asdict(),
                     kwargs['lang'],
                     u.lang))
             return ret
         # No langs, normal query
         return self.query.distinct().filter(*filters) \
                                     .order_by(asc(self.created)) \
                                     .offset(page * limit) \
                                     .limit(limit).all()
     except NoResultFound:
         return []
Beispiel #31
0
def showRestaurants():
    restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
    if 'username' not in login_session:
        return render_template('publicrestaurants.html', restaurants = restaurants)
    else:
        return render_template('restaurants.html', restaurants = restaurants)
Beispiel #32
0
def showCakes():
    cakes = session.query(Cake).order_by(asc(Cake.name))
    return render_template('main.html', cakes=cakes)
Beispiel #33
0
#!/usr/bin/python3
""" lists all State objects that contain the letter a """
from sys import argv
from model_city import City
from sqlalchemy.orm import sessionmaker
from sqlalchemy import asc, update
from model_state import Base, State
from sqlalchemy import (create_engine)

if __name__ == "__main__":

    engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
        argv[1], argv[2], argv[3]),
                           pool_pre_ping=True)
    start = sessionmaker()
    start.configure(bind=engine)
    sta = start()
    for new, new2 in sta.query(State,
                               City).join(City).order_by(asc(City.id)).all():
        print("{}: ({}) {}".format(new.name, new2.id, new2.name))
    sta.close()
Beispiel #34
0
def events(namespace_id, event_public_id, calendar_public_id, title,
           description, location, busy, starts_before, starts_after,
           ends_before, ends_after, limit, offset, view, expand_recurring,
           show_cancelled, db_session):

    query = db_session.query(Event)

    if not expand_recurring:
        if view == 'count':
            query = db_session.query(func.count(Event.id))
        elif view == 'ids':
            query = db_session.query(Event.public_id)

    filters = [
        namespace_id, event_public_id, calendar_public_id, title, description,
        location, busy
    ]
    query = filter_event_query(query, Event, *filters)

    event_criteria = []

    if starts_before is not None:
        event_criteria.append(Event.start < starts_before)

    if starts_after is not None:
        event_criteria.append(Event.start > starts_after)

    if ends_before is not None:
        event_criteria.append(Event.end < ends_before)

    if ends_after is not None:
        event_criteria.append(Event.end > ends_after)

    if not show_cancelled:
        if expand_recurring:
            event_criteria.append(Event.status != 'cancelled')
        else:
            # It doesn't make sense to hide cancelled events
            # when we're not expanding recurring events,
            # so don't do it.
            # We still need to show cancelled recurringevents
            # for those users who want to do event expansion themselves.
            event_criteria.append(
                (Event.discriminator == 'recurringeventoverride')
                | ((Event.status != 'cancelled')
                   & (Event.discriminator != 'recurringeventoverride')))

    event_predicate = and_(*event_criteria)
    query = query.filter(event_predicate)

    if expand_recurring:
        expanded = recurring_events(filters,
                                    starts_before,
                                    starts_after,
                                    ends_before,
                                    ends_after,
                                    db_session,
                                    show_cancelled=show_cancelled)

        # Combine non-recurring events with expanded recurring ones
        all_events = query.filter(Event.discriminator == 'event').all() + \
            expanded

        if view == 'count':
            return {"count": len(all_events)}

        all_events = sorted(all_events, key=lambda e: e.start)
        if limit:
            offset = offset or 0
            all_events = all_events[offset:offset + limit]
    else:
        if view == 'count':
            return {"count": query.one()[0]}
        query = query.order_by(asc(Event.start)).limit(limit)
        if offset:
            query = query.offset(offset)
        # Eager-load some objects in order to make constructing API
        # representations faster.
        all_events = query.all()

    if view == 'ids':
        return [x[0] for x in all_events]
    else:
        return all_events
def default():
    """Default page route."""
    categories = session.query(Category).order_by(asc(Category.id))
    latest = session.query(Product).order_by(
        Product.created_at.desc()).limit(8).all()
    return render_template('index.html', categories=categories, latest=latest)
Beispiel #36
0
def ShowCategories_all():
    all_categories = session.query(Category).order_by(asc(Category.name))
    return render_template('all_categories.html',
                           all_categories=all_categories,
                           login_session=login_session)
Beispiel #37
0
def show_all_lists():
    lists = session.query(TodoList).order_by(asc(TodoList.name))
    return render_template('todoLists.html', lists=lists)
Beispiel #38
0
 def all_tags_selection():
     lista_tags = Tag.query.order_by(asc(Tag.name)).all()
     selection = []
     for t in lista_tags:
         selection.append((t, t))
     return selection
Beispiel #39
0
def showCatalogs():
    catalogs = session.query(Catalog).order_by(asc(Catalog.name))
    if 'username' not in login_session:
        return render_template('publicCatalogs.html', catalogs=catalogs)
    else:
        return render_template('catalogs.html', catalogs=catalogs)
Beispiel #40
0
def show_catalog():
    categories = session.query(Category).order_by(asc(Category.name))
    items = session.query(CategoryItem).order_by(desc(
        CategoryItem.id)).limit(10)
    return render_template('catalog.html', categories=categories, items=items)
Beispiel #41
0
 def get_by_page(cls, order, page, per_page=10):
     sort = desc(Task.id) if order == 'desc' else asc(Task.id)
     return Task.query.order_by(sort).paginate(page, per_page).items
Beispiel #42
0
def showAccounts():
    '''Show all accounts'''
    accounts = session.query(Account).order_by(asc(Account.accountType))
    stocks = session.query(Stock).order_by(asc(Stock.account_id))
    return render_template('stocks.html', accounts=accounts, stocks=stocks)
    def extract_alerts_from_db(self):
        print("Extracting records from db")
        self.alert_list_lock.acquire()
        try:
            start_from = datetime.now()
            if None == self.prev_expirty_time:
                self.prev_expirty_time = get_time_plus_minutes(self.alert_expiry_time)
            else:
                start_from = self.prev_expirty_time;
                self.prev_expirty_time = time_plus_min(self.prev_expirty_time, self.alert_expiry_time)

            # Pull records from db which are sorted in ascending order
            alerts = self.session.query(RescueAlert).filter(RescueAlert.adventure_end_time > start_from, RescueAlert.adventure_end_time < self.prev_expirty_time).order_by(asc(RescueAlert.adventure_end_time)).all()

            self.alerts_buff[len(self.alerts_buff): len(self.alerts_buff)] = alerts
            for al in self.alerts_buff:
                print("\t Adventure Id {} Adventure Name is {}, end-time {}".format(al.id,  al.adventure_name, al.adventure_end_time))
        finally:
            self.alert_list_lock.release()
Beispiel #44
0
 def event_log_get_oldest(self):
     query = model_query(models.EventLog)
     result = query.order_by(asc(models.EventLog.created_at)).limit(1).one()
     return result
Beispiel #45
0
 def get_programs_for_owner(session, owner_name):
     return session.query(Program).filter(
         Program.owner_name == owner_name).order_by(asc(
             Program.prog_order)).all()
Beispiel #46
0
 def get_programs_for_hardware(session, hardware):
     return session.query(Program).filter(
         Program.hardware == hardware).order_by(asc(
             Program.prog_order)).all()
Beispiel #47
0
def index():
    users = User.query.order_by(asc(User.username)).all()
    return render_template('index.html', users = users)
def shownewPlaces():
    place = session.query(Places).order_by(asc(Places.name))
    return render_template('newPlace.html', p=place)
Beispiel #49
0
def showSkishops():
    skishops = session.query(Parent).order_by(asc(Parent.name))
    return render_template('skishops.html', skishops=skishops)
def list_audits():
    page = get_valid_page_or_1()
    try:
        per_page = int(
            request.args.get('per_page',
                             current_app.config['DM_API_SERVICES_PAGE_SIZE']))
    except ValueError:
        abort(400, 'invalid page size supplied')

    audits = AuditEvent.query.order_by(
        desc(AuditEvent.created_at) if convert_to_boolean(
            request.args.get('latest_first')) else asc(AuditEvent.created_at))

    audit_date = request.args.get('audit-date', None)
    if audit_date:
        if is_valid_date(audit_date):
            audit_datetime = datetime.strptime(audit_date, DATE_FORMAT)
            audits = audits.filter(
                AuditEvent.created_at.between(
                    audit_datetime, audit_datetime + timedelta(days=1)))
        else:
            abort(400, 'invalid audit date supplied')

    audit_type = request.args.get('audit-type')
    if audit_type:
        if AuditTypes.is_valid_audit_type(audit_type):
            audits = audits.filter(AuditEvent.type == audit_type)
        else:
            abort(400, "Invalid audit type")

    acknowledged = request.args.get('acknowledged', None)
    if acknowledged and acknowledged != 'all':
        if is_valid_acknowledged_state(acknowledged):
            if convert_to_boolean(acknowledged):
                audits = audits.filter(AuditEvent.acknowledged == true())
            elif not convert_to_boolean(acknowledged):
                audits = audits.filter(AuditEvent.acknowledged == false())
        else:
            abort(400, 'invalid acknowledged state supplied')

    object_type = request.args.get('object-type')
    object_id = request.args.get('object-id')
    if object_type:
        if object_type not in AUDIT_OBJECT_TYPES:
            abort(400, 'invalid object-type supplied')
        if not object_id:
            abort(400, 'object-type cannot be provided without object-id')
        model = AUDIT_OBJECT_TYPES[object_type]
        id_field = AUDIT_OBJECT_ID_FIELDS[object_type]

        ref_object = model.query.filter(id_field == object_id).first()

        if ref_object is None:
            abort(404,
                  "Object with given object-type and object-id doesn't exist")

        audits = audits.filter(AuditEvent.object == ref_object)

    elif object_id:
        abort(400, 'object-id cannot be provided without object-type')

    audits = audits.paginate(page=page, per_page=per_page)

    return jsonify(auditEvents=[audit.serialize() for audit in audits.items],
                   links=pagination_links(audits, '.list_audits',
                                          request.args))
def weight_order():
    # Query all puppies by ascending weight
    puppyWeights = session.query(Puppy).order_by(asc(Puppy.weight)).all()
    for puppyWeight in puppyWeights:
        print puppyWeight.weight
def test_track_tag_mat_view(app):
    """Tests that genre metrics can be queried"""
    with app.app_context():
        db = get_db()

    test_entities = {
        "tracks": [
            {
                "track_id": 1,
                "tags": "",
                "owner_id": 1
            },
            {
                "track_id": 2,
                "owner_id": 1,
                "tags": "pop,rock,electric"
            },
            {
                "track_id": 3,
                "owner_id": 2
            },
            {
                "track_id": 4,
                "owner_id": 2,
                "tags": "funk,pop"
            },
            {
                "track_id": 5,
                "owner_id": 2,
                "tags": "funk,pop"
            },
            {
                "track_id": 6,
                "owner_id": 2,
                "tags": "funk,Funk,kpop"
            },
        ]
    }

    populate_mock_db(db, test_entities)

    with db.scoped_session() as session:
        session.execute("REFRESH MATERIALIZED VIEW tag_track_user")
        user_1_tags = (session.query(TagTrackUserMatview).filter(
            TagTrackUserMatview.owner_id == 1).order_by(
                asc(TagTrackUserMatview.tag),
                asc(TagTrackUserMatview.track_id)).all())
        user_2_tags = (session.query(TagTrackUserMatview).filter(
            TagTrackUserMatview.owner_id == 2).order_by(
                asc(TagTrackUserMatview.tag),
                asc(TagTrackUserMatview.track_id)).all())
        user_4_tags = (session.query(TagTrackUserMatview).filter(
            TagTrackUserMatview.owner_id == 4).all())

        assert len(user_1_tags) == 3
        assert user_1_tags[0].tag == "electric"
        assert user_1_tags[0].track_id == 2
        assert user_1_tags[1].tag == "pop"
        assert user_1_tags[1].track_id == 2
        assert user_1_tags[2].tag == "rock"
        assert user_1_tags[2].track_id == 2

        assert len(user_2_tags) == 6
        assert user_2_tags[0].tag == "funk"
        assert user_2_tags[0].track_id == 4
        assert user_2_tags[1].tag == "funk"
        assert user_2_tags[1].track_id == 5
        assert user_2_tags[2].tag == "funk"
        assert user_2_tags[2].track_id == 6
        assert user_2_tags[3].tag == "kpop"
        assert user_2_tags[3].track_id == 6
        assert user_2_tags[4].tag == "pop"
        assert user_2_tags[4].track_id == 4
        assert user_2_tags[5].tag == "pop"
        assert user_2_tags[5].track_id == 5

        assert not user_4_tags
Beispiel #53
0
def reviews():
	if request.method == 'GET':

		sort = request.args.get('sort', 'asc')
		limit = request.args.get('limit')
		stat = request.args.get('stat')

		if stat != None:
			if sort == 'desc':
				results = Review.query.order_by(desc('reviews.' + stat)).limit(limit).all()
			elif sort == 'asc':
				results = Review.query.order_by(asc('reviews.' + stat)).limit(limit).all()
		else:
			results = Review.query.limit(limit).all()

		json_results = []
		for result in results:
			d = {	'id': 			result.id,
					'created': 		result.created,
					'user_id': 		result.user_id,
					'beer_id': 		result.beer_id,
					'aroma': 		result.aroma,
					'appearance': 	result.appearance,
					'taste': 		result.taste,
					'palate': 		result.palate,
					'bottle_style': result.bottle_style,
					'overall': 		result.overall }
			json_results.append(d)

		return jsonify(review=json_results)

	if request.method == 'POST':

		user_id = 		request.get_json().get('user_id')
		beer_id = 		request.get_json().get('beer_id')
		aroma = 		request.get_json().get('aroma')
		appearance = 	request.get_json().get('appearance')
		taste = 		request.get_json().get('taste')
		palate = 		request.get_json().get('palate')
		bottle_style = 	request.get_json().get('bottle_style')

		if user_id == "" or user_id == None:
			abort(500)
		if beer_id == "" or beer_id == None:
			abort(500)
		if aroma == "" or aroma == None:
			abort(500)
		if appearance == "" or appearance == None:
			abort(500)
		if taste == "" or taste == None:
			abort(500)
		if palate == "" or palate == None:
			abort(500)
		if bottle_style == "" or bottle_style == None:
			abort(500)

		errors = []
		error_flag = False
		if float(aroma) < 0 or float(aroma) > 5:
			error = { 'error' : "Aroma must be between 0.0 and 5.0" }
			errors.append(error)
		if float(appearance) < 0 or float(appearance) > 5:
			error = { 'error' : "Appearance must be between 0.0 and 5.0" }
			errors.append(error)
		if float(taste) < 0 or float(taste) > 10:
			error = { 'error' : "Taste must be between 0.0 and 10.0" }
			errors.append(error)
		if float(palate) < 0 or float(palate) > 5:
			error = { 'error' : "Palate must be between 0.0 and 5.0" }
			errors.append(error)
		if float(bottle_style) < 0 or float(bottle_style) > 5:
			error = { 'error' : "Bottle Style must be between 0.0 and 5.0" }
			errors.append(error)
		if len(errors) > 0:
			return jsonify(response = errors)

		usercheck = User.query.filter_by(id = user_id).first()

		if usercheck == None:
			error = "The user does not exist"
			return jsonify(response = error)

		beercheck = Beer.query.filter_by(id = beer_id).first()

		if beercheck == None:
			error = "The beer does not exist"
			return jsonify(response = error)

		weeklycheck = Review.query.filter_by(user_id = user_id, beer_id = beer_id, posted_this_week = 1).first()

		if weeklycheck != None:
			error = "This user has already reviewed this beer this week"
			return jsonify(response = error)

		ratings = [float(aroma), float(appearance), float(taste), float(palate), float(bottle_style)]
		overall = sum(ratings) / float(len(ratings))

		newReview = Review(
			user_id = user_id,
			beer_id = beer_id,
			aroma = aroma,
			appearance = appearance,
			taste = taste,
			palate = palate,
			bottle_style = bottle_style,
			overall = overall,
			posted_this_week = 1)

		dbsession = db.session()
		dbsession.add(newReview)
		dbsession.commit()

		success = "Review Created"
		return jsonify(response = success)
def six_months():
    # Query all of the puppies that are less than 6 months old organized by the youngest first
    puppies_six_months = session.query(Puppy).filter(
        Puppy.dateOfBirth >= "2015-06-01").order_by(asc(Puppy.dateOfBirth))
    for puppies in puppies_six_months:
        print puppies.dateOfBirth
Beispiel #55
0
def ShowCategories_all_JSON():
    all_categories = session.query(Category).order_by(asc(Category.name))
    return jsonify(all_categories=[i.serialize for i in all_categories])
Beispiel #56
0
def beers():
	if request.method == 'GET':

		sort = request.args.get('sort', 'asc')
		limit = request.args.get('limit')
		stat = request.args.get('stat')

		if stat != None:
			if sort == 'desc':
				results = Beer.query.order_by(desc('beers.' + stat)).limit(limit).all()
			elif sort == 'asc':
				results = Beer.query.order_by(asc('beers.' + stat)).limit(limit).all()
		else:
			results = Beer.query.limit(limit).all()

		json_results = []
		for result in results:
			d = {	'id': 				result.id,
					'name': 			result.name,
					'ibu': 				result.ibu,
					'calories': 		result.calories,
					'abv': 				result.abv,
					'style': 			result.style,
					'brewery_location': result.brewery_location,
					'glass_type': 		result.glass_type }
			json_results.append(d)

		return jsonify(beers=json_results)

	if request.method == 'POST':

		name = 				request.get_json().get('name')
		ibu = 				request.get_json().get('ibu')
		calories = 			request.get_json().get('calories')
		abv = 				request.get_json().get('abv')
		style = 			request.get_json().get('style')
		brewery_location = 	request.get_json().get('brewery_location')
		glass_type = 		request.get_json().get('glass_type')

		user_id = request.get_json().get('user_id')

		if name == "" or name == None:
			abort(500)
		if ibu == "" or ibu == None:
			abort(500)
		if calories == "" or calories == None:
			abort(500)
		if abv == "" or abv == None:
			abort(500)
		if style == "" or style == None:
			abort(500)
		if brewery_location == "" or brewery_location == None:
			abort(500)
		if glass_type == "" or glass_type == None:
			abort(500)

		result = User.query.filter_by(id = user_id).first()

		if result.beer_added_today == 1:
			error = "This user has already added a beer today"
			return jsonify(response = error)

		glasscheck = Glass.query.filter_by(id = glass_type).first()

		if glasscheck == None:
			error = "Invalid beer glass type"
			return jsonify(response = error)

		dbsession = db.session()

		query = dbsession.query(User)
		query = query.filter(User.id == user_id)
		record = query.one()
		record.beer_added_today = 1

		dbsession.commit()

		newBeer = Beer(
			name = name,
			ibu = ibu,
			calories = calories,
			abv = abv,
			style = style,
			brewery_location = brewery_location,
			glass_type = glass_type)

		dbsession = db.session()
		dbsession.add(newBeer)
		dbsession.commit()

		success = "Beer Created"
		return jsonify(response = success)
def showRestaurants():
    restaurants = session.query(Restaurant).order_by(asc(Restaurant.name))
    return render_template('restaurants.html', restaurants=restaurants)
def categoryJSON():
    #query category table from database
    category = session.query(Category).order_by(asc(Category.name)).all()
    return jsonify(categorys=[c.serialize for c in category])
Beispiel #59
0
 def find_all_by_store_wo_page(cls, store_id):
     return cls.query.filter(cls.store_id == store_id).order_by(
         asc(cls.type)).all()
Beispiel #60
0
def showCategories():
    categories = session.query(Category).order_by(asc(Category.name))
    return render_template('categories.html', categories=categories)