def get_fatalities_center(self, fatalities):
     centers = []
     for center in self.session.query(func.avg(Event.longitud).label('longitud'),func.avg(Event.latitud).label('latitud')).filter(Event.fatalities > fatalities):
         centers.append(center.longitud)
         centers.append(center.latitud)
     if len(centers) == 0:
         return [23.992075732837595,4.755856763705008]
     return centers

 def get_events_center(self, country, year):
     centers = []
     for center in self.session.query(func.avg(Event.longitud).label('longitud'),func.avg(Event.latitud).label('latitud')).filter(extract('year', Event.event_date) == year).filter(Event.country_id == country):
         centers.append(center.longitud)
         centers.append(center.latitud)
     if len(centers) == 0:
         return [23.992075732837595,4.755856763705008]
     return centers

def daily_data_expr(flag_expr, value_expr, dry_elev):
    if dry_elev is None:
        dry_elev = -1000
    flag_expr = expression.case([
                                (flag_expr == _M, _M),
                                (flag_expr == None, _O),
                                ],
                                else_=_E)
    flag_expr = expression.case([
                            (func.avg(value_expr) == None, _M),
                            (func.avg(value_expr) < dry_elev, _D),
                            ],
                           else_=func.max(flag_expr)
                           )
    return (flag_expr, func.avg(value_expr))

 def disc_avg(subjectid, sess = None):
     sess = sess or Session()
     #subj = Subjects.get_d(subject, sess)
     sid = subjectid #subj.id
     return sess.query(func.avg(Journal.grade)).\
                       filter(Journal.discipline_id==sid).\
                       scalar()

def get_programme(programme_id):
    res = programmes.query.filter(programmes.id == programme_id).order_by(programmes.programme_name).all()
    for row in res:
        res_sub = projects.query.filter(projects.programme_id == row.id).order_by(projects.project_name).all()
        project_array = []
        for row_sub in res_sub:


            average_points_res = sprints.query.with_entities(func.avg(sprints.delivered_points).label('average')).filter(sprints.project_id == row_sub.id).first()

            if len(average_points_res) > 0:
                if (average_points_res[0]):
                    average_points = round(average_points_res[0])
                else:
                    average_points = 0
            else:
                average_points = 0

            sprint_res_sub = sprints.query.filter(sprints.project_id == row_sub.id).filter(sprints.start_date <= str(date.today())).filter(sprints.end_date >= str(date.today())).order_by(sprints.sprint_number.desc()).first()

            cur_sprint_number = ''
            cur_sprint_end_date = ''
            cur_sprint_rag = ''
            cur_sprint_id = ''
            if sprint_res_sub:
                cur_sprint_number = sprint_res_sub.sprint_number
                cur_sprint_end_date = str(sprint_res_sub.end_date)
                cur_sprint_rag = sprint_res_sub.sprint_rag
                cur_sprint_id = sprint_res_sub.id

            project_array.append({'name': row_sub.project_name, 'id': row_sub.id, 'product_owner': row_sub.product_owner, 'scrum_master': row_sub.scrum_master, 'last_rag': cur_sprint_rag, 'last_sprint': cur_sprint_number, 'last_end_date': cur_sprint_end_date, 'last_sprint_id': cur_sprint_id, 'project_description': row_sub.project_description, 'delivery_manager': row_sub.delivery_manager, 'scrum_tool_link': row_sub.scrum_tool_link, 'average_points': average_points})
        res2 = {'name': row.programme_name, 'id': row.id, 'programme_manager': row.programme_manager, 'service_manager': row.service_manager, 'projects': project_array}
    return Response(json.dumps(res2),  mimetype='application/json')

    def index(self):
        c.name = config['app_conf']['site_name']
        c.title = 'Welcome'
        c.messages = []

        c.session = Session()

        file_q = c.session.query(File)

        first_file = file_q.first()

        if first_file is None:
            return render('/derived/show/please_scan.html')

        c.repo_url = first_file.root
        c.total_size = c.session.query(func.sum(File.size)).one()[0]
        c.file_count = file_q.count()
        c.avg_size = c.session.query(func.avg(File.size)).one()[0]

        res = c.session.execute(select([File.ext,
            func.count(File.ext)]).group_by(File.ext).order_by(func.count(File.ext).desc())).fetchone()

        c.ext_string = res[0]
        c.ext_count = res[1]

        c.revision = c.session.query(func.max(Revision.id)).one()[0]

        c.asset_count = c.session.query(Asset).count()
        c.collection_count = c.session.query(Collection).count()

        return render('/derived/show/index.html')

    def statArtistReleaseCount(self):
        artistAverageCount = self.dbSession.query(func.avg(Artist.releases))
        artistMinimumCount = self.dbSession.query(func.min(Artist.releases)).scalar()
        artistMaximumCount = self.dbSession.query(func.max(Artist.releases)).scalar()
        artistMaximumId = ''
        artistMaxName = ''

        return {
            'title': 'Artist Release Count',
            'class': 'fa-user',
            'average': {
                'type': 'string',
                'value': artistAverageCount,
                'detail': {
                    'text': ''
                }
            },
            'minimum': {
                'type': 'string',
                'value': artistMinimumCount,
                'detail': {
                    'text': 'Many'
                }
            },
            'maximum': {
                'type': 'artist',
                'value': artistMaximumCount,
                'detail': {
                    'id': artistMaximumId,
                    'thumbnailUrl': '/images/artist/thumbnail/' + artistMaximumId,
                    'detailUrl': '/artist/' + artistMaximumId,
                    'text': artistMaxName
                }
            }
        }

def past():
    d_type = request.form['data_type']
    result = db.session.query(func.avg(Record.value)).filter(Record.data_type == d_type, Record.date >= datetime.utcnow() - timedelta(days=4))
    if result[0][0] is not None:
        return json.dumps({'mean': int(result[0][0])})
        #, '0day': datetime.utcnow(), '1day': datetime.utcnow() - timedelta(days=1), , '2day': datetime.utcnow() - timedelta(days=2), , '3day': datetime.utcnow() - timedelta(days=3) }, cls=DecimalEncoder)
    return jsonify(mean = 0) 

def move_import(xmlfile, filename, user, request_form):
    if filename.endswith('.gz'):
        xmlfile = gzip.GzipFile(fileobj=xmlfile, mode='rb', filename=filename)
        filename = filename[:-len('.gz')]

    extension = filename[-4:]
    import_functions = {
        '.xml': old_xml_import,
        '.sml': sml_import,
        '.gpx': gpx_import,
    }

    if extension not in import_functions:
        flash("unknown fileformat: '%s'" % xmlfile.name, 'error')
        return

    import_function = import_functions[extension]
    move = import_function(xmlfile, user, request_form)
    if move:
        move.temperature_avg, = db.session.query(func.avg(Sample.temperature)).filter(Sample.move == move, Sample.temperature > 0).one()

        stroke_count = 0
        for events, in db.session.query(Sample.events).filter(Sample.move == move, Sample.events != None):
            if 'swimming' in events and events['swimming']['type'] == 'Stroke':
                stroke_count += 1

        if 'swimming' in move.activity:
            assert stroke_count > 0

        if stroke_count > 0:
            move.stroke_count = stroke_count

        db.session.commit()
        return move

    def _add_missing_mau(self, table_prefix, days_ago):
        # Locate beginning table
        tables = self._get_filtered_tables(table_prefix, days_ago+30)
        first_date = self._date_from_tablename(tables[0].name)

        grouped_monthly_unique = select([
            monthly_rollup.c.date,
            func.sum(monthly_rollup.c.count).label("count")
        ]).\
            where(monthly_rollup.c.date >= first_date).\
            group_by(monthly_rollup.c.date).\
            order_by(monthly_rollup.c.date)

        # Average them over 6 days prior inclding today for MAU's
        maus = select([
            grouped_monthly_unique.c.date,
            func.avg(grouped_monthly_unique.c.count).over(
                order_by=text("date ROWS 6 PRECEDING")
            ).label("mau")
        ]).\
            order_by(grouped_monthly_unique.c.date)
        results = self._conn.execute(maus).fetchall()

        if not results:
            return

        # Update appropriate rows
        stmt = daily_stats.update().\
            where(daily_stats.c.date == bindparam("update_date")).\
            values(mau=bindparam("mau"))
        self._conn.execute(stmt, [
            {"update_date": x.date, "mau": x.mau}
            for x in results[-days_ago:]
        ])

 def st_avg(stid,sess = None):
     sess = sess or Session()
     #stud = Student.get_st(name,surname, sess)
     sid = stid #stud.id
     return sess.query(func.avg(Journal.grade)).\
                       filter(Journal.student_id==sid).\
                       scalar()

    def _filter_stocks(self, stocks):
        """銘柄を絞り込みます。

        絞り込み条件:
            - 過去の平均出来高が規定の値を上回っている。

        Args:
            stocks: 絞り込み前の銘柄のリスト

        Returns:
            絞り込み後の銘柄のリスト
        """
        filtered_stocks = []

        with start_session() as session:
            for s in stocks:
                avg_volume = session.query(func.avg(History.volume)) \
                    .filter_by(symbol=s.symbol).scalar()

                logger.debug('symbol - avg_volume: {} - {}'.format(
                    s.symbol, avg_volume))

                if avg_volume is not None and float(avg_volume) >= self.volume:
                    # 過去の平均出来高が規定値を上回っている場合、絞り込み後のリストに追加します。
                    filtered_stocks.append(s)

        return filtered_stocks

def reviews(courseid, page = 1):
    if courseid < 1:
        abort(404)
    course= Course.query.get_or_404(courseid)
    reviews = Review.query.filter_by(course_id=course.id).order_by(Review.review_date.desc()).paginate(page, POST_PER_PAGE, False)
    avgrating = (db.session.query(func.avg(models.Review.rating)).filter_by(course_id = course.id)[0])[0]
    totalreviews= reviews.total
    return render_template('course-reviews-list.html', user = g.user, reviews= reviews, course =course, totalreviews=totalreviews, avgrating =avgrating)

 def __call__(self, column_clause, cuboid=None):
     if cuboid and cuboid.fact_count_column is not None:
         count = func.sum(cuboid.fact_count_column)
         return case([(count == 0, 0)], else_=(
             func.sum(column_clause * cuboid.fact_count_column) /
             cast(count,
                  types.Numeric)))
     return func.avg(column_clause)

    def get_average_for_pitches(self, column, pitcher_id=None, pitch_type=None):
        query = self.session.query(func.avg(column))
        if pitcher_id is not None:
            query = query.filter(entities.Pitch.pitcher == pitcher_id)
        if pitch_type is not None:
            query = query.filter(entities.Pitch.pitch_type.like(pitch_type))

        return query.one()

def get_courses(order_by='course_rating', order_direction=DESCENDING, limit=100, offset=0, **kwargs):
    numeric_columns = { 'course_rating', 'instructor_rating', 'workload' }
    all_columns = keys | numeric_columns | { 'grade' }

    exact_keys = { key for key in keys if key in kwargs and kwargs[key] != AVERAGE }
    group_keys = keys - kwargs.keys()

    order_by_name = order_by if (order_by in all_columns and kwargs.get(order_by, '') != AVERAGE) else 'course_rating'

    query = select(
            [courses.columns[key] for key in group_keys] +
            [type_coerce(func.avg(courses.columns[key]), Float).label(key) for key in numeric_columns] +
            [type_coerce(func.avg(case(
                { 'A': 4.0, 'B': 3.0, 'C': 2.0, 'NR': 0.0 },
                value=courses.columns.grade,
                else_=0.0,
            )), Float).label('grade')]
        ).where(
            and_(*[courses.columns[key] == kwargs[key] for key in exact_keys])
        ).group_by(
            *[courses.columns[key] for key in group_keys]
        ).order_by(
            desc(order_by_name) if order_direction == DESCENDING else asc(order_by_name)
        ).limit(min(100, max(1, limit))).offset(max(0, offset))

    results = query.execute().fetchall()

    dict_result = []
    for result in results:
        item = dict(result.items())
        for key in exact_keys:
            item[key] = kwargs[key]
        grade = item['grade']
        if grade >= 3.5:
            grade = 'A'
        elif grade >= 2.5:
            grade = 'B'
        elif grade >= 1.5:
            grade = 'C'
        else:
            grade = 'NR'
        item['grade'] = grade
        dict_result.append(item)

    return dict_result
    return query

 def last_day(self):
     query = db.session.query(
             func.round(func.avg(Reading.temperature)), 
             func.round(func.avg(Reading.humidity)),
             Reading.timestamp
         )\
         .filter_by(channel=self.channel)\
         .group_by(func.strftime('%H', Reading.timestamp))\
         .order_by(Reading.timestamp.desc())\
         .limit(30)
     return [
         {
             "temperature": r[0],
             "humidity": r[1],
             "timestamp": r[2]
         } for r in query.all()
     ]

 def resolve_average_result(self, args, context, info):
     vote_cls = self.get_vote_class()
     voting_avg = self.db.query(func.avg(getattr(vote_cls, 'vote_value'))).filter_by(
         vote_spec_id=self.id,
         tombstone_date=None,
         idea_id=self.criterion_idea_id).first()
     # when there is no votes, query.first() equals (None,)
     avg = voting_avg[0]
     return avg

def analyze_day(day = "2014-08-22"):
    hour = func.cast(func.substr(trips.c.joreohitusaika_time,1,2), sa.Integer)
    cols = [trips.c.tulopysakki, hour, func.avg(trips.c.ohitusaika_ero).label('delay_avg')]
    new_conds = conditions
    new_conds.append(trips.c.tapahtumapaiva==day)
    conds = and_(*new_conds)
    groupcols = [trips.c.tulopysakki, hour]
    ts = run(cols, conds, groupcols, n_limit=None)
    save_multi_json(ts, "../site/hourly_stop_delays_%s.json" % day)

 def get_avg_rating(self):
     avg_rating = db.session\
         .query(func.avg(Comment.rating))\
         .filter_by(user_id=self.id)
     if avg_rating[0][0] is None:
         avg = 0
     else:
         avg = avg_rating[0][0]
     return "{0:.2f}".format(avg)