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)

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

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

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

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

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)

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

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

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

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)

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)

    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 

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

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

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

    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