def handle_save(self):
        data, errors = self.extractData()
        journal_note = data.pop('note')
        now = datetime.now()

        if errors:
            self.flash(_(u'Es ist ein Fehler aufgetreten!'))
            return FAILURE
        number = data.get('number', 0)
        if number:
            session = get_session('ukhvoucher')
            try:
                from sqlalchemy.sql.functions import max
                oid = int(session.query(max(Voucher.oid)).one()[0]) + 1
            except:
                oid = 100000
            from ukhvoucher.models import Generation
            import json

            p = int(session.query(max(Generation.oid)).one()[0] or 0) + 1
            generation = Generation(
                oid=p,
                date=now.strftime('%Y-%m-%d'),
                type=data['kategorie'],
                data=json.dumps('Manuelle Erzeugung'),
                user=self.request.principal.id,
                uoid=oid
            )

            for idx in range(number):
                voucher = Voucher(
                    creation_date=datetime.now().strftime('%Y-%m-%d'),
                    status=CREATED,
                    cat = data['kategorie'],
                    user_id=self.context.oid,
                    generation_id=p,
                    oid=oid)
                oid += 1
                session.add(voucher)
            session.add(generation)

            # journalize
            entry = JournalEntry(
                date=datetime.now().strftime('%Y-%m-%d'),
                userid=self.request.principal.id,
                action=u"Berechtigungsscheine manuell erstellt",
                #action=u"Add:%s" % self.context.model.__label__,
                oid=str(self.context.oid),
                note=journal_note)
            session.add(entry)

            # redirect
            self.flash(_(u"%s Berechtigungsscheine erstellt" % number))
            self.redirect(self.application_url())
            return SUCCESS
        else:
            self.flash(_(u"The demand must be for at least 1 voucher."))
            self.redirect(self.url(self.context))
            return FAILURE

 def caprate(self, attacker=None):
     maxcap = PA.getfloat("roids", "maxcap")
     mincap = PA.getfloat("roids", "mincap")
     if not attacker or not self.value:
         return maxcap
     modifier = (float(self.value) / float(attacker.value)) ** 0.5
     return max(mincap, min(maxcap * modifier, maxcap))

 def _nextOrdering(self, blogId):
     '''
     Provides the next ordering.
     '''
     max = self.session().query(fn.max(BlogPostMapped.Order)).filter(BlogPostMapped.Blog == blogId).scalar()
     if max: return max + 1
     return 1

    def get_list(cls, **kw):
        # SELECT client.clientid, job_bytes, max_job FROM client
        # LEFT JOIN (SELECT job.clientid, SUM(job.jobbytes) AS job_bytes FROM job
        # GROUP BY job.clientid) AS vsota ON vsota.clientid = client.clientid
        # LEFT JOIN (SELECT job.clientid, MAX(job.schedtime) AS max_job FROM job
        # GROUP BY job.clientid) AS last_job ON last_job.clientid = client.clientid;
        sum_stmt = Job.query\
            .with_entities(Job.clientid, func.sum(Job.jobbytes).label('job_sumvolbytes'))\
            .group_by(Job.clientid)\
            .subquery('stmt_sub')
        last_stmt = Job.query\
            .with_entities(Job.clientid, func.max(Job.starttime).label('job_maxschedtime')).filter(Job.jobstatus == 'T')\
            .group_by(Job.clientid)\
            .subquery('stmt_max')
        objects = cls.query.with_entities(Client, 'job_sumvolbytes', 'job_maxschedtime', func.count(Job.jobid).label('num_jobs'))\
            .outerjoin(Job, Client.clientid == Job.clientid)\
            .outerjoin(sum_stmt, sum_stmt.c.clientid == Client.clientid)\
            .outerjoin(last_stmt, last_stmt.c.clientid == Client.clientid)\
            .group_by(cls, 'job_sumvolbytes', 'job_maxschedtime')\
            .all()

        # ugly hack since sqlite returns strings for job_maxschedtime
        # TODO: report upstream to sqlalchemy
        if DBSession.bind.dialect.name == 'sqlite':
            def convert_datetime(l):
                if l.job_maxschedtime:
                    l.job_maxschedtime = datetime.datetime.strptime(l.job_maxschedtime, '%Y-%m-%d %H:%M:%S')
                return l
            objects = map(convert_datetime, objects)
        return objects

def testing_function_4(query_module):
    models_module = sqlalchemy_models
    query = query_module.get_query(max(models_module.Author.id))

    rows = query.all()
    result = map(extract_row, rows)
    return str(result)

 def _nextCId(self):
     '''
     Provides the next change Id.
     '''
     max = self.session().query(fn.max(BlogPostMapped.CId)).scalar()
     if max: return max + 1
     return 1

def find_score(filename, id, userid):
    """
    the import here is done inside because else a cyclic import situation arrises but as this
    method runs inside another process so the time consumed doesnt matter
    :param filename:
    :param id:
    :return:
    """
    from manage import app

    with app.app_context():
        with open(filename) as file:
            length = len(file.read())
        question = Question.query.filter(Question.id == id).first()
        maxS = question.max_score
        print(length, maxS)
        score = ((maxS - length) / maxS) * 100
        if score < 1:
            score = 1
        submission = Submission(user_id=userid, question_id=id, \
                                result=True, result_score=score, result_message="Solved")

        db.session.add(submission)
        db.session.commit()
        db.create_all()
        all_submissions = db.session.query(functions.max(Submission.result_score)).filter(Submission.user_id==userid).group_by(
            Submission.question_id).all()
        user = User.query.filter(User.id == userid).first()
        user.total_score = sum((x[0] for x in all_submissions))
        db.session.commit()
        print("done")

    def impacted_hls(self, *args):
        """
        Renvoie une requête portant sur les services de haut niveau impactés.

        @param args: Liste d'éléments à récupérer dans la requête.
        @type args: Une C{DeclarativeBase} ou une liste de C{Column}s.
        @return: Une C{Query} portant sur les éléments demandés.
        @rtype: C{sqlalchemy.orm.query.Query}
        """
        from vigilo.models.tables import HighLevelService, \
                                            ImpactedHLS, ImpactedPath

        if not args:
            args = [HighLevelService]

        imp_hls1 = aliased(ImpactedHLS)
        imp_hls2 = aliased(ImpactedHLS)

        subquery = DBSession.query(
            functions.max(imp_hls1.distance).label('distance'),
            imp_hls1.idpath
        ).join(
            (ImpactedPath, ImpactedPath.idpath == imp_hls1.idpath)
        ).filter(ImpactedPath.idsupitem == self.idsupitem
        ).group_by(imp_hls1.idpath).subquery()

        services_query = DBSession.query(*args).distinct(
        ).join(
            (imp_hls2, HighLevelService.idservice == imp_hls2.idhls),
            (subquery, subquery.c.idpath == imp_hls2.idpath),
        ).filter(imp_hls2.distance == subquery.c.distance)

        return services_query

def clean_stale_tasks():

    from maproulette.models import db, Task, Action
    from sqlalchemy.sql.functions import max
    from datetime import datetime, timedelta
    import pytz

    current_time = datetime.now(pytz.utc)
    stale_threshold = current_time - timedelta(hours=1)
    counter = 0

    for task in (
        db.session.query(Task)
        .filter(Task.currentaction.in_(["assigned", "editing"]))
        .join(Task.actions)
        .group_by(Task.id)
        .having(max(Action.timestamp) < stale_threshold)
        .all()
    ):
        task.append_action(Action("available"))
        db.session.add(task)
        print "setting task %s to available" % (task.identifier)
        counter += 1
    db.session.commit()
    print "done. %i tasks made available" % counter

 def query_ordered(cls):
     # order by most recent last_seen OR key transaction
     newest_date = functions.max(
         functions.coalesce(User.last_seen, 0),
         functions.coalesce(KeyTransaction.start, 0)
     )
     query = Key.query.outerjoin(Key.holder).outerjoin(Key.current_transaction)
     return query.order_by(db.desc(newest_date))

def index():
    if current_user.is_authenticated():
        all_questions = Question.query.all()
        all_submissions = db.session.query(Submission.question_id,functions.max(Submission.result_score),Submission.result_message,Submission.result).filter(Submission.user_id==current_user.id).group_by(
            Submission.question_id).all()
        return render_template("index.html", all_quest=all_questions,allsubmission =all_submissions)
    flash("you need to login to see the questions", category="warning")
    return redirect(url_for("auth.login"))

def getInvoiceId():
    from ukhvoucher.models import Invoice
    from sqlalchemy.sql.functions import max
    session = get_session('ukhvoucher')
    try:
        oid = int(session.query(max(Invoice.oid)).one()[0]) + 1
    except:
        oid = 100000
    return unicode(oid)

def get_stale_assigned_tasks():
    """returns all assigned tasks that are stale"""

    # select t.id from tasks t, actions a where
    # a.task_id = t.id and t.currentaction = 'assigned'
    # group by t.id having now() - max(a.timestamp) < interval '1 day';
    return db.session.query(Task).filter_by(
        currentaction='assigned').join(Task.actions).group_by(
        Task.id).having(max(Action.timestamp) > stale_threshold).all()

 def _setup_next_sequence(cls, *args, **kwargs):
     """Compute the next available PK, based on the 'pk' database field."""
     session = cls.FACTORY_SESSION
     model = cls.FACTORY_FOR
     pk = getattr(model, model.__mapper__.primary_key[0].name)
     max_pk = session.query(max(pk)).one()[0]
     if isinstance(max_pk, int):
         return max_pk + 1 if max_pk else 1
     else:
         return 1

 def stats(self):
     """
     select host, count(pk), min(created), max(created) from responses group by host;
     """
     q = select([
         responses.c.host.label('host'),
         functions.count(responses.c.pk).label('amount'),
         functions.min(responses.c.created),
         functions.max(responses.c.created),
     ]).group_by('host').order_by(desc('amount'))
     return self.db.execute(q).fetchall()

        def insert(form, amount):
            now = datetime.datetime.now()
            principal = form.request.principal
            session = get_session('ukhvoucher')
            kat = form._iface.getName()
            cat_vouchers = principal.getVouchers(cat=kat)
            if len(cat_vouchers) > 0:
                form.flash(u'Die Berechtigungsscheine wurde für diese Kategorie bereits erzeugt.', type="info")
                url = form.application_url()
                return SuccessMarker('Success', True, url=url)
            try:
                oid = int(session.query(max(Voucher.oid)).one()[0]) + 1
            except:
                oid = 100000

            try:
                p = int(session.query(max(Generation.oid)).one()[0]) + 1
            except:
                p=1
            generation = Generation(
                oid=p,
                date=now.strftime('%Y-%m-%d'),
                type=form._iface.getName(),
                data=json.dumps(data),
                user=principal.id,
                uoid=oid
            )

            for i in range(amount):
                oid += 1
                voucher = Voucher(
                    oid = oid,
                    creation_date = now.strftime('%Y-%m-%d'),
                    status = CREATED,
                    cat = form._iface.getName(),
                    user_id = principal.oid,
                    generation_id = p,
                    )
                session.add(voucher)

            session.add(generation)

    def process_elos(self, session, game_type_cd=None):
        if game_type_cd is None:
            game_type_cd = self.game_type_cd

        # we do not have the actual duration of the game, so use the 
        # maximum alivetime of the players instead
        duration = 0
        for d in session.query(sfunc.max(PlayerGameStat.alivetime)).\
                    filter(PlayerGameStat.game_id==self.game_id).\
                    one():
            duration = d.seconds

        scores = {}
        alivetimes = {}
        for (p,s,a) in session.query(PlayerGameStat.player_id, 
                PlayerGameStat.score, PlayerGameStat.alivetime).\
                filter(PlayerGameStat.game_id==self.game_id).\
                filter(PlayerGameStat.alivetime > timedelta(seconds=0)).\
                filter(PlayerGameStat.player_id > 2).\
                all():
                    # scores are per second
                    scores[p] = s/float(a.seconds)
                    alivetimes[p] = a.seconds

        player_ids = scores.keys()

        elos = {}
        for e in session.query(PlayerElo).\
                filter(PlayerElo.player_id.in_(player_ids)).\
                filter(PlayerElo.game_type_cd==game_type_cd).all():
                    elos[e.player_id] = e

        # ensure that all player_ids have an elo record
        for pid in player_ids:
            if pid not in elos.keys():
                elos[pid] = PlayerElo(pid, game_type_cd)

        for pid in player_ids:
            elos[pid].k = KREDUCTION.eval(elos[pid].games, alivetimes[pid],
                    duration)
            if elos[pid].k == 0:
                del(elos[pid])
                del(scores[pid])
                del(alivetimes[pid])

        elos = self.update_elos(elos, scores, ELOPARMS)

        # add the elos to the session for committing
        for e in elos:
            session.add(elos[e])

        if game_type_cd == 'duel':
            self.process_elos(session, "dm")

    def update(self, engine):
        firstid = self.segment_table.first_new_id

        with engine.begin() as conn:
            # delete any objects that might have been deleted
            # Note: a relation also might get deleted from this table
            # because it lost its relevant tags.
            conn.execute(self.data.delete().where(self.id_column.in_
                                            (self.src.select_modify_delete())))
            # Collect all changed relations in a temporary table
            sel = select([sqlf.func.unnest(self.segment_table.data.c.rels).label("id")],
                         distinct=True)\
                       .where(self.segment_table.data.c.id >= firstid)

            if self.hierarchy_table is not None:
                sel = select([self.hierarchy_table.data.c.parent], distinct=True)\
                       .where(self.hierarchy_table.data.c.child.in_(
                                sel.union(self.src.select_add_modify()))).alias()

                hmax = self.hierarchy_table.data.alias()
                crosstab = select([hmax.c.child, sqlf.max(hmax.c.depth).label("lvl")])\
                             .group_by(hmax.c.child).alias()

                sel = select([sel.c.parent.label("id"), crosstab.c.lvl])\
                        .where(sel.c.parent == crosstab.c.child)

            conn.execute('DROP TABLE IF EXISTS __tmp_osgende_routes_updaterels')
            conn.execute(CreateTableAs('__tmp_osgende_routes_updaterels', sel,
                         temporary=False))
            tmp_rels = Table('__tmp_osgende_routes_updaterels',
                             MetaData(), autoload_with=conn)

            conn.execute(self.data.delete()\
                           .where(self.id_column.in_(select([tmp_rels.c.id]))))

        # reinsert those that are not deleted
        w = self.segment_table.osmtables.way.data
        self._stm_ways = select([w.c.nodes]).where(w.c.id == bindparam('id'))\
                                .compile(engine)


        if self.hierarchy_table is None:
            inssel = self.src.select_all(self.src.data.c.id.in_(tmp_rels.select()))
            self.insert_objects(engine, inssel)
        else:
            for level in range(6, 0, -1):
                where = self.src.data.c.id.in_(select([tmp_rels.c.id])
                                                 .where(tmp_rels.c.lvl == level))
                self.insert_objects(engine, self.src.select_all(where))
        # drop the temporary table
        tmp_rels.drop(engine)

 def add(self, name, password, _crypt_strength=None):
     if self.name_taken(name):
         raise ValueError('Name already exists')
     if _crypt_strength is None:
         salt = bcrypt.gensalt()
     else:
         salt = bcrypt.gensalt(_crypt_strength)
     db = self.backend._db
     max_id = (db.query(functions.max(self.item_table.id)).one()[0] or 0)
     user = self.item_table(
             id=max_id + 1,
             name=name,
             normalized_name=make_identifier(name),
             password=bcrypt.hashpw(password, salt),
             joined_at=datetime.utcnow(),
         )
     db.add(user)
     db.flush()
     return self.item_class(self.backend, user)

    def get(self):
        """ List of all projects """
        opts = PROJECTS_OPTS_PARSER.parse_args()
        filters = PROJECT_FILTERS_PARSER.parse_args()
        filters = clean_attrs(filters)

        query = Project.query

        if not current_user.is_authenticated():
            query = query.filter_by(public=True)

        if opts['order'] == 'recent':
            query = (
                query.
                join(Project.jobs, isouter=True).
                group_by(Project).
                order_by(sql_func.max(Job.create_ts).desc().nullslast())
            )

        if filters:
            query = query.filter(*[
                getattr(Project, field) == value
                for field, value in filters.items()
            ])

        marshaler = dict(items=ALL_LIST_ROOT_FIELDS['items'])
        values = dict(items=query.all())

        args = PROJECT_LIST_PARSER.parse_args()

        if args['meta']:
            marshaler['meta'] = ALL_LIST_ROOT_FIELDS['meta']
            values['meta'] = {'total': query.count()}
            values['meta'].update(Project.get_status_summary(filters))

        if args['latest_job']:
            marshaler['items'] = ITEMS_MARSHALER_LATEST_JOB

        return marshal(values, marshaler)