def update(self):
     srch = self.request.params.get('search', '')
     if srch:
         self.users = list(Session.query(User)
             .filter(User.email.contains('%%%s%%'%srch)).order_by(asc('id')).all())
     elif self.request.params.get('showall', ''):
         self.users = list(Session.query(User).order_by(asc('id')).all())

 def _get_by_disabled_from_db(context, disabled):
     if disabled:
         return context.session.query(api_models.CellMapping).filter_by(
             disabled=true()).order_by(asc(api_models.CellMapping.id)).all()
     else:
         return context.session.query(api_models.CellMapping).filter_by(
             disabled=false()).order_by(asc(
                 api_models.CellMapping.id)).all()

 def get_accounts_and_loans(self):
     if not self.accounts_and_loans:
         self.accounts_and_loans = AccountsTable.query.filter(AccountsTable.user == self.user_id)\
         .filter(AccountsTable.balance != 0)\
         .outerjoin((UsersTable, AccountsTable.name == UsersTable.id))\
         .add_columns(UsersTable.name, UsersTable.slug)\
         .order_by(asc(AccountsTable.type)).order_by(asc(AccountsTable.id))
     return self.accounts_and_loans

def get_stat(context, request):
    user_name = None
    if hasattr(request, 'cookies') and 'sk' in request.cookies.keys() and 'sk' in request.session and \
                    request.session['sk'] == request.cookies['sk'] and 'u_name' in request.session:
        user_name = request.session['u_name']

    session = DBSession()
    uiks_from_db = session.query(Uik, Uik.point.x, Uik.point.y) \
        .join('geocoding_precision') \
        .join('tik') \
        .join('region')

    clauses = []
    if request.POST:
        if exist_filter_parameter('geocoding_precision', request):
            clauses.append(Uik.geocoding_precision_id == request.POST['geocoding_precision'])
        if exist_filter_parameter('is_applied', request):
            clauses.append(Uik.is_applied == (request.POST['is_applied'] == 'True'))
        if exist_filter_parameter('number_official', request):
            clauses.append(Uik.number_official == request.POST['number_official'])
        if exist_filter_parameter('region', request):
            clauses.append(Uik.region_id == int(request.POST['region']))
        if exist_filter_parameter('place_voting', request):
            clauses.append(Uik.place_voting.ilike('%' + request.POST['place_voting'].encode('UTF-8').strip() + '%'))
        if exist_filter_parameter('tik', request):
            clauses.append(Uik.tik_id == int(request.POST['tik']))
        if exist_filter_parameter('user_id', request):
            user_uiks_subq = (session.query(distinct(UikVersions.uik_id).label("uik_id"))
                              .filter(UikVersions.user_id == int(request.POST['user_id']))) \
                              .subquery()
            uiks_from_db = uiks_from_db.join(user_uiks_subq, and_(Uik.id == user_uiks_subq.c.uik_id))

    uiks_from_db = uiks_from_db.filter(*clauses)

    if 'jtSorting' in request.params:
        sort = request.params['jtSorting']
        sort = sort.split(' ')
        if sort[1] == 'ASC':
            uiks_from_db = uiks_from_db.order_by(asc(get_sort_param(sort[0])))
        if sort[1] == 'DESC':
            uiks_from_db = uiks_from_db.order_by(desc(get_sort_param(sort[0])))
    else:
        uiks_from_db = uiks_from_db.order_by(asc(Uik.number_official))

    count = uiks_from_db.count()

    uiks_from_db = uiks_from_db.offset(request.params['jtStartIndex']) \
        .limit(request.params['jtPageSize']) \
        .all()

    records = [create_uik_stat(uik) for uik in uiks_from_db]
    session.close()

    return Response(json.dumps({
        'Result': 'OK',
        'Records': records,
        'TotalRecordCount': count
    }), content_type='application/json')

def flavor_get_by_flavor_id(context, flavor_id, read_deleted):
    """Returns a dict describing specific flavor_id."""
    result = _flavor_get_query(context, read_deleted=read_deleted).\
                        filter_by(flavorid=flavor_id).\
                        order_by(asc("deleted"), asc("id")).\
                        first()
    if not result:
        raise Exception("plop")
    return _dict_with_extra_specs(result)

    def jobs(self, _id=None, category_id=None, page=0, per_page=None, sort='category_id.asc', include_runs=False):
        session = self.session
        categories = list(self.categories())

        query = session.query(Job)

        if _id is not None:
            query = query.filter_by(id=_id)

        if category_id is not None:
            query = query.filter_by(category_id=category_id)

        field, order = sort.split('.', 1)
        if order == 'asc':
            query = query.order_by(asc(getattr(Job, field)))
            if field != 'id':
                query = query.order_by(asc(Job.id))
        elif order == 'desc':
            query = query.order_by(desc(getattr(Job, field)))
            if field != 'id':
                query = query.order_by(asc(Job.id))

        if per_page is not None:
            query = query.limit(per_page)

        if page > 0:
            query = query.offset(page * per_page)

        for job in query:
            data = {
                'id': job.id,
                'category_id': job.category_id,
                'name': job.name,
                'interval': job.interval,
                'next_run': job.next_run,
                'last_run_start': job.last_run_start,
                'last_run_stop': job.last_run_stop,
                'last_run_status': job.last_run_status,
                'last_run_text': job.last_run_text(),
                'current_status': job.current_status or 'none',
                'routing_key': job.routing_key,
                'command': job.command,
                'locked_by': job.locked_by,
            }
            if include_runs:
                data['runs'] = chunks_to_runs(self.last_event_chunks_for_job(job.id, 20))
            category = filter(lambda c: c['id'] == job.category_id, categories)
            if len(category) == 1:
                data['category'] = category[0]
            yield data

    def get(self):

        catalog_id = self.get_argument_int('c', 1)
        page_size = self.get_argument_int('sepa', 20)
        cur_page = self.get_argument_int('p', 1)
        by = self.get_argument('by', 'updated')
        sort = self.get_argument('sort', 'DESC')

        by_exp = desc(by) if sort == 'DESC' else asc(by)
        start = (cur_page - 1) * page_size
        stop = start + page_size

        apps = self.db2.query(Appliance).filter_by(
            catalog_id=catalog_id).filter_by(
            isprivate=False).order_by(by_exp)

        total = apps.count()
        apps = apps.slice(start, stop)
            
        catalogs = self.db2.query(ApplianceCatalog).all()
        for c in catalogs:
            c.total = self.db2.query(Appliance.id).filter_by(
                catalog_id = c.id ).filter_by(
                isprivate=False).count()

        page_html = pagination(self.request.uri, total,  page_size, cur_page)

        d = { 'title': self.title,
              'appliances': apps,
              'catalogs': catalogs,
              'cur_catalog': catalog_id,
              'page_html': page_html }

        self.render("appliance/index.html", **d)

    def sorting(self):
        """Construct the query, by adding sorting(ORDER BY) on the columns needed to be applied on
        """
        sorting = []

        Order = namedtuple('order', ['name', 'dir'])

        if self.request_values.get('iSortCol_0') \
            and self.request_values.get('iSortingCols') > 0:

            for i in range(int(self.request_values['iSortingCols'])):
                sorting.append(Order( self.columns[int(self.request_values['iSortCol_'+str(i)])].column_name,
                                      self.request_values['sSortDir_'+str(i)]))

        for sort in sorting:
            tmp_sort_name = sort.name.split('.')
            obj = getattr(self.sqla_object, tmp_sort_name[0])
            if isinstance(obj.property, RelationshipProperty): # Ex: ForeignKey
            # Ex: address.description => description => addresses.description
                sort_name = "".join(tmp_sort_name[1:])
                if not sort_name:
                    # Find first piramry key
                    sort_name = obj.property.table.primary_key.columns \
                        .values()[0].name
                tablename = obj.property.table.name
            else: #-> ColumnProperty
                sort_name = sort.name
                tablename = self.sqla_object.__tablename__
            sort_name = "%s.%s" % (tablename, sort_name)
            self.query = self.query.order_by(
                asc(sort_name) if sort.dir == 'asc' else desc(sort_name))

 def categories(self):
     session = self.session
     for category in session.query(Category).order_by(asc(Category.id)):
         yield {
             'id': category.id,
             'name': category.name,
         }

def set_from_and_to_dates():
    try:
        index_str = 'CREATE INDEX ' +\
                    '"ix_GEMSData__ODM_SQL_SiteID_ODM_Param_asc" ON ' +\
                    '"tbl_TexasHIS_Vector_TWDB_ODM_Data" ("ODM_SQL_SiteID",' +\
                    '"ODM_Param_Date" ASC);'
        gems_engine.execute(index_str)
    except OperationalError:
        pass
    sites_query = gems_session.query(GEMSSite)
    site_count = sites_query.count()
    for index, site in enumerate(page_query(sites_query)):
        if index == 1 or index % 1000 == 0:
            print "setting from and to dates for %s of %s sites" % (
                index, site_count)

        if len(gems_session.dirty) > 1500:
            gems_session.commit()

        from_value = site.values.order_by(asc('ODM_Param_Date')).first()
        to_value = site.values.order_by(desc('ODM_Param_Date')).first()
        if from_value:
            site.ODM_Param_FromDate = from_value.ODM_Param_Date
            site.ODM_Param_ToDate = to_value.ODM_Param_Date
        else:
            # if for some reason there are no values for a site
            gems_session.delete(site)
    gems_session.commit()

    def get(self):
        # These are seperate from post() so they are not all required
        # as they just define ways to search for items
        parser = reqparse.RequestParser()
        parser.add_argument('item_id', type=int)
        parser.add_argument('status', type=int)
        parser.add_argument('ascending', type=bool, default=False)
        parser.add_argument('event_id', type=int)
        args = parser.parse_args()
        
        actions_query = Action.query

        # By default newest items are returned first
        if args.ascending:
            actions_query = actions_query.order_by(asc(Action.date))
        else:
            actions_query = actions_query.order_by(desc(Action.date))

        if args.item_id != None:
            actions_query = actions_query.filter(Action.item_id == args.item_id)

        if args.status != None:
            actions_query = actions_query.filter(Action.status == args.status)

        if args.event_id != None:
            actions_query = actions_query.filter(Action.event_id == args.event_id)

        return actions_query.all()

def prune_keep_record(job_id, keep, storage):
    event = storage.session.query(Event).filter_by(job_id=job_id).\
        order_by(asc(Event.id)).limit(1).first()

    min_id = None
    if event is not None:
        min_id = event.id

    events = storage.session.query(Event).filter_by(job_id=job_id).\
        order_by(desc(Event.id)).limit(keep)
    event_ids = [e.id for e in events]
    if len(event_ids) == 0:
        logger.info('No events for {0}'.format(job_id))
        return

    max_id = min(event_ids)
    if min_id == max_id:
        logger.info('Min and max event ids for {0} are the same: {1} - {2}'.format(  # noqa
            job_id, min_id, max_id))
        return

    if min_id > max_id:
        logger.info('Min event id for {0} is larger than max event id: {1} - {2}'.format(  # noqa
            job_id, min_id, max_id))
        return

    logger.info('Job ID {0}, Pruning events {1} - {2}'.format(
        job_id, min_id, max_id))

    stmt = Event.__table__.delete()\
                          .where(between(Event.id, min_id, max_id))\
                          .where(Event.job_id == job_id)
    storage._engine.execute(stmt)
    storage.session.commit()

def construct_query(table, query_tree):

    select_list = query_tree.get("select")

    q = select(col_list(table, select_list))

    distinct = query_tree.get("distinct")

    if distinct:
        q = q.distinct()

    raw_where = query_tree.get("where")

    if raw_where:
        q = q.where(construct_clauses(table, raw_where))

    order_by = query_tree.get("order_by")

    if order_by:
        for order_col in order_by:
            col_name = order_col["col"]
            type = order_col["type"]
            col = get_col(table, col_name)

            if type == "asc" or type == None:
                q = q.order_by(asc(col))
            elif type == "desc":
                q = q.order_by(desc(col))

    limit = query_tree.get("limit")
    if limit:
        q = q.limit(limit)

    return q

def test5():
    """
    Context menu
    """
    dbinfo = create_and_open()
    session = dbinfo.Session()
    #fill_a_db(session,dbinfo.mapped_classes)
    
    
    from OpenElectrophy.gui.contextmenu import context_menu
    
    treedescription = TreeDescription(
                            dbinfo = dbinfo,
                            table_children = { 
                                                    'Block' : ['Segment' ],
                                                    'Segment' : [ 'AnalogSignal'],
                                                    },
                            columns_to_show = { },
                            table_on_top = 'Block',
                            table_order = {'Block' : desc('name'),
                                                    'Segment' : asc('name'),
                                                    #~ 'segment' : desc('name'),
                                                    } ,
                            )
    app = QApplication([ ])
    w = QtSqlTreeView(session = session, treedescription = treedescription, context_menu = context_menu)
    w.show()
    sys.exit(app.exec_())

def test4():
    url = 'sqlite://'
    dbinfo = open_db(url, 
                        object_number_in_cache = 3000,
                        #~ memmap_path = './memmap1',
                        #~ memmap_path = None,
                        )
    
    import pickle
    treedescription = TreeDescription(
                            dbinfo = dbinfo,
                            table_children = { 
                                                    'Block' : ['Segment' ],
                                                    'Segment' : [ 'AnalogSignal'],
                                                    },
                            columns_to_show = { },
                            table_on_top = 'Block',
                            table_order = { 'Block' : desc('name'),
                                                    'Segment' : asc('name'),
                                                    #~ 'Segment' : desc('name'),
                                                    } ,
                            )
    session = dbinfo.Session()
    session.query('Block')
    for k,v in treedescription.__dict__.items():
        print k, v
    treedescription2 = pickle.loads(pickle.dumps(treedescription))
    print treedescription2.name
    print treedescription2.table_order
    for k,v in treedescription2.table_order.items():
        print k, str(v)

    def try_to_get_phrase(template, phrase, url, portal_id=None, allow_html=''):

        a_filter = dict(template=template, name=phrase, portal_id=portal_id)

        # TODO: OZ by OZ: this functions exists because we sometemes inmsert recort in flashing process (see on_value_changed decorator)
        # and we can`t use ORM
        def insert_record(**values):
            from profapp import utils
            g.db().execute(('INSERT INTO "%s" (template,   name,    portal_id, allow_html,   url,  %s) '
                            'VALUES           (:template, :name, :portal_id, :allow_html, :url,  :%s)') %
                           (TranslateTemplate.__tablename__, ', '.join(TranslateTemplate.languages),
                            ", :".join(TranslateTemplate.languages)),
                           params=utils.dict_merge(a_filter, {'allow_html': allow_html, 'url': url},
                                                   {l: phrase for l in TranslateTemplate.languages}, values))
            return db(TranslateTemplate, **a_filter).first()

        exist = db(TranslateTemplate, **a_filter).first()

        if portal_id and not exist:
            exist_for_another = db(TranslateTemplate, template=template, name=phrase,
                                   portal_id=TranslateTemplate.exemplary_portal_id).first()
            # TODO: OZ by OZ: how to select template portal? now we grab phrases from most recent portal, and there can be some unappropriate values
            if not exist_for_another:
                exist_for_another = db(TranslateTemplate, template=template, name=phrase).filter(
                    TranslateTemplate.portal != None).order_by(expression.asc(TranslateTemplate.cr_tm)).first()
            if exist_for_another:
                return insert_record(**{l: getattr(exist_for_another, l) for l in TranslateTemplate.languages})
        if not exist:
            return insert_record()

        return exist

    def get(self):

        catalog_id = int( self.get_argument('c', 1) )
        page_size = int( self.get_argument('sepa', 10) )
        cur_page = int( self.get_argument('p', 1) )
        by = self.get_argument('by', 'updated')
        sort = self.get_argument('sort', 'DESC')

        by_exp = desc(by) if sort == 'DESC' else asc(by)
        start = (cur_page - 1) * page_size
        stop = start + page_size

        apps = self.db2.query(Appliance).filter_by(
            catalog_id=catalog_id).order_by(by_exp).slice(start, stop)
            
        catalogs = self.db2.query(ApplianceCatalog).all()
        for c in catalogs:
            c.total = self.db2.query(Appliance.id).filter_by( catalog_id = c.id ).count()

        pagination = Pagination(
            total = self.db2.query(Appliance.id).count(),
            page_size = page_size, cur_page = cur_page )

        page_html = pagination.html( self.get_page_url )

        d = { 'title': "Appliance Home",
              'appliances': apps,
              'catalogs': catalogs,
              'cur_catalog': catalog_id,
              'page_html': page_html }

        self.render("appliance/index.html", **d)

    def get_index(self):

        by = self.get_argument('by', 'id')
        order = self.get_argument('order', 'DESC')
        if ( order == 'DESC' ):
            order_func = desc( by )
        else:
            order_func = asc( by )

        page_size = int(self.get_argument('sepa', 10))
        cur_page = int(self.get_argument('p', 1))

        start = (cur_page - 1) * page_size
        stop = start + page_size

        JOB_LIST = self.db2.query(Job).order_by( order_func )
        JOB_LIST = JOB_LIST.slice(start, stop)


        JOB_TOTAL = self.db2.query(Job.id).count()


        page_html = Pagination(
            total = JOB_TOTAL, page_size = page_size,
            cur_page = cur_page ).html(self.get_page_url)

        d = { 'title': 'Jobs',
              'JOB_TOTAL': JOB_TOTAL,
              'JOB_LIST': JOB_LIST,
              'page_html': page_html }

        self.render('admin/job/index.html', **d)

    def get_entity_events(self, stored_entity_id, after=None, until=None, limit=None, query_ascending=True,
                          results_ascending=True):
        try:
            query = self.db_session.query(SqlStoredEvent)
            query = query.filter_by(stored_entity_id=stored_entity_id)
            if query_ascending:
                query = query.order_by(asc(SqlStoredEvent.timestamp_long))
            else:
                query = query.order_by(desc(SqlStoredEvent.timestamp_long))

            if after is not None:
                if query_ascending:
                    query = query.filter(SqlStoredEvent.timestamp_long > timestamp_long_from_uuid(after))
                else:
                    query = query.filter(SqlStoredEvent.timestamp_long >= timestamp_long_from_uuid(after))

            if until is not None:
                if query_ascending:
                    query = query.filter(SqlStoredEvent.timestamp_long <= timestamp_long_from_uuid(until))
                else:
                    query = query.filter(SqlStoredEvent.timestamp_long < timestamp_long_from_uuid(until))

            if limit is not None:
                query = query.limit(limit)
            events = self.map(from_sql, query)
            events = list(events)
        finally:
            self.db_session.close()
        if results_ascending != query_ascending:
            events.reverse()
        return events

    def get_post_votes(self):

        page_size = self.get_argument_int('ps', 20)
        cur_page = self.get_argument_int('p', 1)
        by = self.get_argument('by', 'created')
        order = self.get_argument_int('order', 1)

        start = (cur_page - 1) * page_size
        stop = start + page_size

        VOTE_LIST = self.db.query(ForumPostVote)

        if by not in ['id', 'created', 'updated']:
            by = 'id'

        total = VOTE_LIST.count()

        sort_by_obj = desc(by) if order else asc(by)
        VOTE_LIST = VOTE_LIST.order_by(
            sort_by_obj).slice(start, stop).all()

        page_html = pagination(
            self.request.uri, total, page_size, cur_page )

        d = { 'title': _('Forum Post Vote History'),
              'VOTE_LIST': VOTE_LIST,
              'VOTE_TOTAL': total,
              'page_html': page_html }

        self.render('forum/index_post_votes.html', **d)