def get(self,tablename):
     try:
         session = Session()
         M = make_table_model(tablename.encode('utf-8'))   
         #filter_args = [ getattr(M,k)==v[0] for k,v in self.request.arguments.items()]
         _params = {}
         [_params.update({k: v[0]}) for k, v in self.request.arguments.items()]
         logger.info(tablename+str(_params))
         filter_args = utils.get_filter_args(M, _params)
         if filter_args:
             models =  session.query(M).filter(*filter_args).order_by(desc('id')).limit(100)
         else:
             models =  session.query(M).order_by(desc('id')).limit(100)
         logger.debug(models)
         models = [ [ getattr(model,c.name) for c in M.__table__._columns] for model in models]
         clms = map(lambda x:x.name, M.__table__._columns)
         # hide the primary_key 
         result = map(lambda x: dict(zip(clms[1:], x[1:])), models)
         for item in result:
             for k in item:
                 if  type(item[k])==datetime:
                     item[k] = item[k].strftime("%Y-%m-%d %H:%M:%S")
                 elif type(item[k])==unicode:
                     item[k] = item[k].strip()
         self.write(json.dumps(result))
     except BaseException, e:
         self.write(json.dumps({'msg':'Request Error'}))
         logger.error(traceback.format_exc(e))

 def get_cases(status, current_user, user=False, QA=False, current_user_perms=False, case_perm_checker=None,
               case_man=False):
     q = session.query(Case)
     if status != 'All' and status != "Queued":
         q = q.filter_by(currentStatus=status)
     elif status == "Queued":
         q = q.filter_by(currentStatus=CaseStatus.OPEN).join('tasks').filter(Task.currentStatus == TaskStatus.QUEUED)
     if user is True:
         q = q.join('tasks').join(Task.task_roles)
         if QA:
             q = q.filter(and_(UserTaskRoles.user_id == current_user.id, UserTaskRoles.role.in_(UserTaskRoles.qa_roles)))
         else:
             q = q.filter(and_(UserTaskRoles.user_id == current_user.id, UserTaskRoles.role.in_(UserTaskRoles.inv_roles)))
         return q.order_by(desc(Case.creation_date)).all()
     else:
         cases = q.order_by(desc(Case.creation_date)).all()
         output = []
         for case in cases:
             if (case_man is True and case.principle_case_manager is None and case.secondary_case_manager is None) \
                     or case_man is False:
                 try:
                     case_perm_checker(current_user, case, "view")
                     output.append(case)
                 except Forbidden:
                     pass
         return output

def general(env):
    """
        Default page.
    """
    if loggedin(env):
        tmpl = jinjaenv.get_template('meinpage.html')

        userid = env['beaker.session']['loggedin_id']

        userinfo = ut.info(userid)

        user_commits =  Session.query(Commit).filter(Commit.user_id == userid
                ). order_by(sqlalchemy.desc(Commit.id)).limit(5).all()

        script_commits = Session.query(Commit).join(
                (Script, Script.id == Commit.script_id)).filter(
                 Script.owner_id == userid).order_by(
                 sqlalchemy.desc(Commit.id)).limit(5).all()

        return template_render(tmpl,
               {'session' : env['beaker.session'],
                'user' : userinfo['user'],
                'ttc' : userinfo['time']['commit_time'],
                'tc' : userinfo['time']['commit_amount'],
                'own_commits' : user_commits,
                'script_commits' : script_commits
               })

    else:
        tmpl = jinjaenv.get_template('base.html')

        return template_render(tmpl, {'session' : env['beaker.session']} )

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

def get_organizations(name=None):
    ''' Regular response option for organizations.
    '''

    filters = request.args
    filters, querystring = get_query_params(request.args)

    if name:
        # Get one named organization.
        filter = Organization.name == raw_name(name)
        org = db.session.query(Organization).filter(filter).first()
        if org:
            return jsonify(org.asdict(True))
        else:
            # If no org found
            return jsonify({"status": "Resource Not Found"}), 404

    # Get a bunch of organizations.
    query = db.session.query(Organization)
    # Default ordering of results
    ordering = desc(Organization.last_updated)

    for attr, value in filters.iteritems():
        if 'q' in attr:
            query = query.filter("organization.tsv_body @@ plainto_tsquery('%s')" % value)
            ordering = desc(func.ts_rank(Organization.tsv_body, func.plainto_tsquery('%s' % value)))
        else:
            query = query.filter(getattr(Organization, attr).ilike('%%%s%%' % value))

    query = query.order_by(ordering)
    response = paged_results(query, int(request.args.get('page', 1)), int(request.args.get('per_page', 10)), querystring)

    return jsonify(response)

def sorter(default_sort_id, kwd):
    """
    Initialize sorting variables
    """
    SortSpec = namedtuple('SortSpec', ['sort_id', 'order', 'arrow', 'exc_order'])

    sort_id = kwd.get('sort_id')
    order = kwd.get('order')

    # Parse the default value
    if sort_id == "default":
        sort_id = default_sort_id

    # Create the sort
    if order == "asc":
        _order = sa.asc( sort_id )
    elif order == "desc":
        _order = sa.desc( sort_id )
    else:
        # In case of default
        order = "desc"
        _order = sa.desc( sort_id )

    # Create an arrow icon to put beside the ordered column
    up_arrow = "↑"
    down_arrow = "↓"
    arrow = " "

    if order == "asc":
        arrow += down_arrow
    else:
        arrow += up_arrow

    return SortSpec(sort_id, order, arrow, _order)

 def _set_orderby_desc(self, query, model, limit,
                       last_id, offset, descending, orderby):
     """Return an updated query with the proper orderby and desc."""
     if orderby == 'fav_user_ids':
         n_favs = func.coalesce(func.array_length(model.fav_user_ids, 1), 0).label('n_favs')
         query = query.add_column(n_favs)
     if orderby in ['created', 'updated', 'finish_time']:
         if descending:
             query = query.order_by(desc(
                                         cast(getattr(model,
                                                      orderby),
                                              TIMESTAMP)))
         else:
             query = query.order_by(cast(getattr(model, orderby), TIMESTAMP))
     else:
         if orderby != 'fav_user_ids':
             if descending:
                 query = query.order_by(desc(getattr(model, orderby)))
             else:
                 query = query.order_by(getattr(model, orderby))
         else:
             if descending:
                 query = query.order_by(desc("n_favs"))
             else:
                 query = query.order_by("n_favs")
     if last_id:
         query = query.limit(limit)
     else:
         query = query.limit(limit).offset(offset)
     return query

def user_page(user_name):
    # Munge the user_name, and hope
    user_name = user_name.replace('_', ' ').lower()
    user = g.conn.session.query(User).filter(func.lower(User.username) == user_name).one()
    stats = {
        'query_count': g.conn.session.query(func.count(Query.id)).filter(Query.user_id == user.id).scalar(),
        'stars_count': g.conn.session.query(func.count(Star.id)).filter(Star.user_id == user.id).scalar()
    }
    draft_queries = g.conn.session.query(Query) \
        .filter(Query.user_id == user.id) \
        .filter_by(published=False) \
        .order_by(desc(Query.last_touched)) \
        .limit(10)
    published_queries = g.conn.session.query(Query)\
        .filter(Query.user_id == user.id)\
        .filter_by(published=True)\
        .order_by(desc(Query.last_touched))\
        .limit(10)
    stars = g.conn.session.query(Star).join(Star.query) \
        .options(joinedload(Star.query))\
        .filter(Star.user_id == user.id) \
        .order_by(desc(Star.timestamp))\
        .limit(10)
    return render_template(
        "user.html",
        display_user=user,
        user=get_user(),
        stats=stats,
        draft_queries=draft_queries,
        published_queries=published_queries,
        stars=stars
    )

    def __init__(self, user, reportid, filters=None):
        "Init"
        self.dbsession = Session
        self.user = user
        self.reportid = reportid
        self.model = None
        self.isaggr = False
        self.filters = filters

        queryfield = getattr(Message, REPORTS[self.reportid]['address'])
        orderby = REPORTS[reportid]['sort']
        if (self.reportid in ['3', '4', '7', '8']
            and self.user.is_superadmin
            and not self.filters):
            # domains
            self.isaggr = True
            if self.reportid in ['3', '4']:
                #src
                self.model = SrcMessageTotals
                self.query = self.dbsession\
                            .query(SrcMessageTotals.id.label('address'),
                            SrcMessageTotals.total.label('count'),
                            SrcMessageTotals.volume.label('size'))\
                            .order_by(desc(orderby))
            else:
                #dst
                self.model = DstMessageTotals
                self.query = self.dbsession\
                            .query(DstMessageTotals.id.label('address'),
                            DstMessageTotals.total.label('count'),
                            DstMessageTotals.volume.label('size'))\
                            .order_by(desc(orderby))
        else:
            # emails & relays
            self.query = self.dbsession.query(queryfield.label('address'),
                                    func.count(queryfield).label('count'),
                                    func.sum(Message.size).label('size'))
            if self.reportid != '10':
                self.query = self.query.filter(queryfield != u'')\
                            .group_by(queryfield).order_by(desc(orderby))
            else:
                self.query = self.query.filter(queryfield != u'127.0.0.1')\
                            .group_by(queryfield).order_by(desc(orderby))
        if self.isaggr:
            uquery = AggrFilter(self.query)
        else:
            uquery = UserFilter(self.dbsession,
                                self.user,
                                self.query)
        if not self.reportid in ['5', '6', '7', '8']:
            self.query = uquery()
        if self.reportid in ['5', '6', '7', '8']:
            if not self.user.is_superadmin:
                uquery.setdirection('in')
                self.query = uquery()
            else:
                flf = self.model.id if self.isaggr else Message.to_domain
                self.query = self.query.filter(flf\
                            .in_(self.dbsession.query(Domain.name)\
                            .filter(Domain.status == True)))

def show_all(sid,Rss=False):
	if Rss==True:
		article=se.query(Article).order_by(desc('date')).all()
	else:
		article=se.query(Article).order_by(desc('date')).limit(SKIP).offset(sid*SKIP).all()
	se.close()
	return article

    def index(self, trans, **kwd):
        """
        GET /api/workflows

        Displays a collection of workflows.

        :param  show_published:      if True, show also published workflows
        :type   show_published:      boolean
        """
        show_published = util.string_as_bool( kwd.get( 'show_published', 'False' ) )
        rval = []
        filter1 = ( trans.app.model.StoredWorkflow.user == trans.user )
        if show_published:
            filter1 = or_( filter1, ( trans.app.model.StoredWorkflow.published == true() ) )
        for wf in trans.sa_session.query( trans.app.model.StoredWorkflow ).filter(
                filter1, trans.app.model.StoredWorkflow.table.c.deleted == false() ).order_by(
                desc( trans.app.model.StoredWorkflow.table.c.update_time ) ).all():
            item = wf.to_dict( value_mapper={ 'id': trans.security.encode_id } )
            encoded_id = trans.security.encode_id(wf.id)
            item['url'] = url_for('workflow', id=encoded_id)
            item['owner'] = wf.user.username
            rval.append(item)
        for wf_sa in trans.sa_session.query( trans.app.model.StoredWorkflowUserShareAssociation ).filter_by(
                user=trans.user ).join( 'stored_workflow' ).filter(
                trans.app.model.StoredWorkflow.deleted == false() ).order_by(
                desc( trans.app.model.StoredWorkflow.update_time ) ).all():
            item = wf_sa.stored_workflow.to_dict( value_mapper={ 'id': trans.security.encode_id } )
            encoded_id = trans.security.encode_id(wf_sa.stored_workflow.id)
            item['url'] = url_for( 'workflow', id=encoded_id )
            item['owner'] = wf_sa.stored_workflow.user.username
            rval.append(item)
        return rval

def get_measurements_by_count(device_id, count, offset=1, interval=LIVE):
    """
    Get specific count of measurements

    :param device_id: uuid of device
    :param count: count of measurements
    :param offset: offset in page
    :param interval: interval of aggregation
    :return: list of measurements dto
    """
    device = Device.query.filter_by(uuid=device_id).first()
    measurements = []

    # Extract specific count of measurements.
    if interval == LIVE:
        measurements = device.measurements.order_by(desc(Measurement.timestamp)).\
            limit(count).offset(count * (offset - 1)).all()
    elif interval == HOUR:
        measurements = Hour.query.filter(Hour.device_id == device.id).\
            order_by(desc(Hour.timestamp)).\
            limit(count).offset(count * (offset - 1)).all()
    elif interval == DAY:
        measurements = Day.query.filter(Day.device_id == device.id).\
            order_by(desc(Day.timestamp)).\
            limit(count).offset(count * (offset - 1)).all()
    elif interval == WEEK:
        measurements = Week.query.filter(Week.device_id == device.id).order_by(desc(Week.timestamp)).\
            limit(count).offset(count * (offset - 1)).all()

    return measurements_to_dto(measurements, count=count, offset=offset)

def renderGroupId(sid, page=1):

	group = Translators.query.filter(Translators.id==sid).scalar()

	if group is None:
		flash(gettext('Group/Translator not found? This is probably a error!'))
		return redirect(url_for('renderGroupsTable'))

	names = [tmp.name for tmp in group.alt_names]

	feeds = Feeds.query.options(joinedload('tags')) \
		.filter(Feeds.srcname.in_(names))            \
		.order_by(desc(Feeds.published))


	items = Releases.query.filter(Releases.tlgroup==group.id).order_by(desc(Releases.published)).all()
	feed_entries = feeds.paginate(page, app.config['SERIES_PER_PAGE'])

	ids = []
	for item in items:
		ids.append(item.series)

	series = Series.query.filter(Series.id.in_(ids)).order_by(Series.title).all()

	return render_template('group.html',
						   series        = series,
						   releases      = items,
						   sequence_item = feed_entries,
						   group         = group,
						   wiki          = wiki_views.render_wiki("Group", group.name)
						   )

    def filter(self, sql):
        plain = []
        sort = "id"
        for item in self.query:
            if item.startswith("sort:"):
                sort = item.partition(":")[2]
            elif item.startswith("score:"):
                score = int(item.partition(":")[2])
                sql = sql.filter(Post.score == score)
            else:
                t = Tag.get(item)
                if not t:
                    logger.info("Couldn't find tag for '%s' - shortcutting", item)
                    sql = sql.filter("1=0")
                elif t.is_plain_tag():
                    plain.append(t.name)

        if plain:
            sql = sql.join(Post.tags).filter(Tag.name.in_(plain)).group_by(Post.id).having(func.count(Post.id) == len(plain))

        if sort == "score":
            sql = sql.order_by(desc(Post.score))
        elif sort == "-posted":
            sql = sql.order_by(asc(Post.id))
        else:  # "posted"
            sql = sql.order_by(desc(Post.id))

        return sql

def _get_traps(db, offset=0, limit=50, host=None, oid=None, severity=None):
    now = datetime.utcnow()

    active_query = (db
        .query(Notification)
        .filter(or_(
            Notification.expires >= now,
            Notification.expires == None
         ))
        .order_by(desc(Notification.sent))
    )
    active_query = filter_query(active_query, host, oid, severity)

    total_active = active_query.count()
    traps = active_query.offset(offset).limit(limit).all()
    num_active = len(traps)

    if num_active:
        remaining_offset = 0
    else:
        remaining_offset = offset - total_active
        if remaining_offset < 0:
            remaining_offset = 0

    if num_active < limit:
        expired_query = (db
            .query(Notification)
            .filter(Notification.expires < now)
            .order_by(desc(Notification.sent))
        )
        expired_query = filter_query(expired_query, host, oid, severity)
        traps += expired_query.offset(remaining_offset).limit(limit - num_active).all()

    return traps, num_active

def _get_last_trx_id_for_namespace(namespace_id, db_session):
    q = bakery(lambda session: session.query(Transaction.id))
    q += lambda q: q.filter(
        Transaction.namespace_id == bindparam('namespace_id'))
    q += lambda q: q.order_by(desc(Transaction.created_at)).\
        order_by(desc(Transaction.id)).limit(1)
    return q(db_session).params(namespace_id=namespace_id).one()[0]

def indiv_details(request):

    params=int(request.matchdict['id'])
    join_table = join(SatTrx, ObjectsCaracValues, SatTrx.ptt == cast(ObjectsCaracValues.value, Integer)
        ).join(Individual, ObjectsCaracValues.object==Individual.id) 

    query=select([ObjectsCaracValues.value.label('id'), Individual.id.label('ind_id'),Individual.survey_type.label('survey_type'), Individual.status.label('status')
        , Individual.monitoring_status.label('monitoring_status'), Individual.birth_date.label('birth_date'), Individual.ptt.label('ptt'),ObjectsCaracValues.begin_date.label('begin_date'),ObjectsCaracValues.end_date.label('end_date')]
        ).select_from(join_table
        ).where(and_(SatTrx.model.like('GSM%'),ObjectsCaracValues.carac_type==19,ObjectsCaracValues.object_type=='Individual')
        ).where(ObjectsCaracValues.value==params).order_by(desc(ObjectsCaracValues.begin_date))
   
    data=DBSession.execute(query).first()
    transaction.commit()
    if data['end_date'] == None :
        end_date=datetime.datetime.now()
    else :
        end_date=data['end_date'] 

    result=dict([ (key[0],key[1]) for key in data.items()])
    print(result)
    result['duration']=(end_date.month-data['begin_date'].month)+(end_date.year-data['begin_date'].year)*12
    
    query = select([V_Individuals_LatLonDate.c.date]
                     ).where(V_Individuals_LatLonDate.c.ind_id == result['ind_id']
                     ).order_by(desc(V_Individuals_LatLonDate.c.date)).limit(1)
     
    lastObs=DBSession.execute(query).fetchone()
    result['last_observation']=lastObs['date'].strftime('%d/%m/%Y')
    if result['birth_date']!= None:
        result['birth_date']=result['birth_date'].strftime('%d/%m/%Y')
    del result['begin_date'], result['end_date']
    print (result)
    return result

	def get(self, groupname, name):
		"""
		 Review the articles for a specific feed on this key.
		"""
		key = auth()

		feed = Feed.query.filter(and_(Feed.name == name, Feed.key == key)).first()
		if not feed: abort(404)

		per_page = 10

		parser = restful.reqparse.RequestParser()
		parser.add_argument("page",type=int, help="", required=False, default=1)
		parser.add_argument("content",type=bool, help="", required=False, default=None)
		args = parser.parse_args()

		# Return a list of the JSONified Articles ordered by descending creation date and paginated.
		if args.content == True:
			return [a.jsonify() for a in \
					Article.query.filter(and_(Article.key == key, Article.content != None, Article.feed == feed))
					.order_by(desc(Article.created)).paginate(args.page, per_page).items
			]
		elif args.content == False:
			return [a.jsonify() for a in \
					Article.query.filter(and_(Article.key == key, Article.content == None, Article.feed == feed))
					.order_by(desc(Article.created)).paginate(args.page, per_page).items
			]

		return [a.jsonify() for a in \
				Article.query.filter(and_(Article.key == key, Article.feed == feed))
				.order_by(desc(Article.created)).paginate(args.page, per_page).items
		]

def getContacts(search,baoxiang):
    start=0
    limit=30
    if search!='':
        search="%"+search+"%"
        if baoxiang!="":
            baoxiang="%"+baoxiang+"%"
            #objs = Contact.objects.filter((Q(hetongbh__icontains=search) | Q(yiqibh__icontains=search)) & Q(baoxiang=baoxiang)).order_by('-yujifahuo_date')[start:start+limit]
            objs=session.query(PartsContact).filter(
                    and_(
                        or_(PartsContact.hetongbh.like(search),PartsContact.yiqibh.like(search)),
                        PartsContact.baoxiang.like(baoxiang)
                    )
                ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit]
        else:
             objs=session.query(PartsContact).filter(
                        or_(PartsContact.hetongbh.like(search),PartsContact.yiqibh.like(search))
                ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit] # Contact.objects.filter(Q(hetongbh__icontains=search) | Q(yiqibh__icontains=search)).order_by('-yujifahuo_date')[start:start+limit]
    else:
        if baoxiang!="":
            baoxiang="%"+baoxiang+"%"
            objs=session.query(PartsContact).filter(
                        PartsContact.baoxiang.like(baoxiang)
                ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit]
        else:
            objs=session.query(PartsContact).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit]
    return objs

    def GetProducts(
        self, tenantId, pageNo=0, pageSize=50, searchField=None, searchValue=None, status=None, supplierId=None
    ):
        if not tenantId:
            return None
        query = DBSession.query(Product).filter(Product.TenantId == tenantId)

        if searchField:
            if searchField == "Name" and searchValue:
                query = query.filter(Product.Name.like("%s%%" % searchValue)).order_by(Product.Name)
            elif searchField == "Barcode" and searchValue:
                query = query.filter(Product.Barcode == searchValue)
            elif searchField == "MRP" and searchValue:
                query = query.filter(Product.MRP == searchValue)
            elif searchField == "SellPrice" and searchValue:
                query = query.filter(Product.SellPrice == searchValue)
            elif searchField == "SuppierName" and searchValue:
                query = query.join(Supplier).filter(Supplier.Name == searchValue)

        if status:
            query = query.filter(Product.Status == status)

        if supplierId:
            query = query.filter(Product.SupplierId == supplierId)

        query = query.order_by(desc(Product.UpdatedOn), desc(Product.CreatedOn))

        lstItems = query.offset(pageNo).limit(pageSize).all()
        return lstItems, query.count()