def check_venue_bookings( start, end, venue, sitting=None ):
    """
    return all sittings (but sitting if given) a venue is booked for
    in the period
    """
    assert( type(start) == datetime.datetime )
    assert( type(end) == datetime.datetime ) 
    session = Session()
    b_filter = sql.and_(sql.or_( 
                    sql.between(schema.sittings.c.start_date, start, end), 
                    sql.between(schema.sittings.c.end_date, start, end),
                    sql.between(start, schema.sittings.c.start_date, 
                                schema.sittings.c.end_date),
                    sql.between(end, schema.sittings.c.start_date, 
                                schema.sittings.c.end_date)
                        ),
                    schema.sittings.c.venue_id == venue.venue_id)
    if sitting:
        if sitting.sitting_id:
            b_filter = sql.and_(b_filter,
                        schema.sittings.c.sitting_id != sitting.sitting_id)
    query = session.query(BookedVenue).filter(b_filter)
    venues = query.all()
    #session.close()
    return venues

def get_available_venues( start, end, sitting=None ):
    """get all venues that are not booked for a sitting
    (but sitting if given)
    in the given time period 
    SQL:
    SELECT * 
    FROM venues 
    WHERE venues.venue_id NOT IN (SELECT sitting.venue_id 
        FROM sitting 
        WHERE (sitting.start_date BETWEEN '2000-01-01' AND '2000-01-02' 
            OR sitting.end_date BETWEEN '2000-01-01'  AND '2000-01-02'
            OR '2000-01-01'  BETWEEN sitting.start_date AND sitting.end_date 
            OR '2000-01-02'  BETWEEN sitting.start_date AND sitting.end_date) 
        AND sitting.venue_id IS NOT NULL)
    """
    session = Session()
    query = session.query(domain.Venue)
    b_filter = sql.and_(
        sql.or_( 
            sql.between(schema.sitting.c.start_date, start, end), 
            sql.between(schema.sitting.c.end_date, start, end),
            sql.between(start, schema.sitting.c.start_date, 
                schema.sitting.c.end_date),
            sql.between(end, schema.sitting.c.start_date, 
                schema.sitting.c.end_date)
        ),
        schema.sitting.c.venue_id != None)
    if sitting:
        if sitting.sitting_id:
            b_filter = sql.and_(b_filter,
                schema.sitting.c.sitting_id != sitting.sitting_id)
    query = query.filter(sql.not_(schema.venue.c.venue_id.in_(
                sql.select( [schema.sitting.c.venue_id] ).where(b_filter) )))
    venues = query.all()
    return venues

    def __call__(self, user_ids, session):
        """
        Parameters:
            user_ids    : list of mediawiki user ids to restrict computation to
            session     : sqlalchemy session open on a mediawiki database

        Returns:
            {
                user id: 1 if they're a rolling new active editor, 0 otherwise
                for all cohort users, or all users that have edits in the time period
            }
        """
        number_of_edits = int(self.number_of_edits.data)
        rolling_days = int(self.rolling_days.data)
        end_date = self.end_date.data
        start_date = end_date - timedelta(days=rolling_days)

        newly_registered = session.query(Logging.log_user) \
            .filter(Logging.log_type == 'newusers') \
            .filter(Logging.log_action == 'create') \
            .filter(between(Logging.log_timestamp, start_date, end_date))

        filtered_new = self.filter(
            newly_registered, user_ids, column=Logging.log_user
        ).subquery()

        rev_user = label('user_id', Revision.rev_user)
        ar_user = label('user_id', Archive.ar_user)
        count = label('count', func.count())

        revisions = session.query(rev_user, count)\
            .filter(between(Revision.rev_timestamp, start_date, end_date))\
            .filter(Revision.rev_user.in_(filtered_new))\
            .group_by(Revision.rev_user)

        archived = session.query(ar_user, count)\
            .filter(between(Archive.ar_timestamp, start_date, end_date))\
            .filter(Archive.ar_user.in_(filtered_new))\
            .group_by(Archive.ar_user)

        bot_user_ids = session.query(MediawikiUserGroups.ug_user)\
            .filter(MediawikiUserGroups.ug_group == 'bot')\
            .subquery()

        new_edits = revisions.union_all(archived).subquery()
        new_edits_by_user = session.query(new_edits.c.user_id)\
            .filter(new_edits.c.user_id.notin_(bot_user_ids))\
            .group_by(new_edits.c.user_id)\
            .having(func.SUM(new_edits.c.count) >= number_of_edits)

        metric_results = {r[0]: {self.id : 1} for r in new_edits_by_user.all()}

        if user_ids is None:
            return metric_results
        else:
            return {
                uid: metric_results.get(uid, self.default_result)
                for uid in user_ids
            }

def listenerdata(start, stop):
    from rfk.site import app

    app.logger.warn(start)
    app.logger.warn(stop)
    stop = parse_datetimestring(stop)
    start = parse_datetimestring(start)
    app.logger.warn(start)
    app.logger.warn(stop)
    ret = {'data': {}, 'shows': []}

    streams = Stream.query.all()
    for stream in streams:
        ret['data'][str(stream.mount)] = []
        #just set an initial stating point from before the starting point
        stats = stream.statistic.get(stop=start, num=1, reverse=True)
        c = 0
        for stat in stats:
            c = stat.value
        if not stats:
            c = 0
        ret['data'][str(stream.mount)].append((to_timestamp(to_user_timezone(start)), int(c)))

    #fill in the actual datapoints
    streams = Stream.query.all()
    for stream in streams:
        stats = stream.statistic.get(start=start, stop=stop)
        for stat in stats:
            ret['data'][str(stream.mount)].append(
                (to_timestamp(to_user_timezone(stat.timestamp)), int(stat.value)))

    streams = Stream.query.all()
    for stream in streams:
        stats = stream.statistic.get(stop=stop, num=1, reverse=True)
        for stat in stats:
            c = stat.value
        if not stats:
            c = 0
        ret['data'][str(stream.mount)].append((to_timestamp(to_user_timezone(stop)), int(c)))

    #get the shows for the graph
    shows = Show.query.filter(between(Show.begin, start, stop) \
                              | between(Show.end, start, stop)).order_by(Show.begin.asc()).all()
    for show in shows:
        sstart = to_timestamp(to_user_timezone(show.begin))
        if show.end:
            send = to_timestamp(to_user_timezone(show.end))
        else:
            send = to_timestamp(to_user_timezone(now()))
        ret['shows'].append({'name': show.name,
                             'b': sstart,
                             'e': send})
    return jsonify(ret)

    def __call__(self, user_ids, session):
        """
        Parameters:
            user_ids    : list of mediawiki user ids to restrict computation to
            session     : sqlalchemy session open on a mediawiki database

        Returns:
            dictionary from user ids to: 1 if they're a rolling active editor, 0 if not
        """
        number_of_edits = int(self.number_of_edits.data)
        rolling_days = int(self.rolling_days.data)
        end_date = self.end_date.data
        start_date = end_date - timedelta(days=rolling_days)

        rev_user = label("user_id", Revision.rev_user)
        ar_user = label("user_id", Archive.ar_user)
        count = label("count", func.count())

        revisions = (
            session.query(rev_user, count)
            .filter(between(Revision.rev_timestamp, start_date, end_date))
            .group_by(Revision.rev_user)
        )
        revisions = self.filter(revisions, user_ids, column=Revision.rev_user)

        archived = (
            session.query(ar_user, count)
            .filter(between(Archive.ar_timestamp, start_date, end_date))
            .group_by(Archive.ar_user)
        )
        archived = self.filter(archived, user_ids, column=Archive.ar_user)

        bot_user_ids = (
            session.query(MediawikiUserGroups.ug_user).filter(MediawikiUserGroups.ug_group == "bot").subquery()
        )

        edits = revisions.union_all(archived).subquery()
        edits_by_user = (
            session.query(edits.c.user_id)
            .filter(edits.c.user_id.notin_(bot_user_ids))
            .group_by(edits.c.user_id)
            .having(func.SUM(edits.c.count) >= number_of_edits)
        )

        metric_results = {r[0]: {self.id: 1} for r in edits_by_user.all()}

        if user_ids is None:
            return metric_results
        else:
            return {uid: metric_results.get(uid, self.default_result) for uid in user_ids}

def list_files(request):
    """
    LIST all the files uploaded in the last 5 hours

    """
    from sqlalchemy.sql.expression import between
    from datetime import datetime, timedelta
    session = DBSession()
    now = datetime.now()

    # timeframe could be selectable from an interface for instance
    # easy to implement: analyze pars or set default if missing...
    timeframe = {'hours': 5, 'minutes': 0, 'seconds': 0}
    delta = now - timedelta(**timeframe)
    result = session.query(MyModel)\
        .filter(between(MyModel.timestamp, delta, now))\
        .order_by(MyModel.timestamp.desc())

    if result.count() > 0:
        files = []
        for res in result:
            print res.path
            files.append({'id': res.id,
                          'file_name': res.name,
                          'file_size': res.size,
                          'file_type': res.filetype,
                          'download_url': 'http://localhost:9000/getfile/%s' % res.id
                          })
        return files
    else:
        # Could add a No Content (204) response
        return []

 def getFilter(date):
     return sql.or_(
         sql.between(date, 
             schema.group.c.start_date, schema.group.c.end_date),
         sql.and_(
             schema.group.c.start_date<=date, 
             schema.group.c.end_date==None))

def top_maps_by_times_played_q(cutoff_days, region = None, game_type_cd = None):
    """
    Query to retrieve the top maps by the amount of times it was played
    during a date range.

    Games older than cutoff_days days old are ignored.
    """
    # only games played during this range are considered
    right_now = datetime.utcnow()
    cutoff_dt = right_now - timedelta(days=cutoff_days)

    top_maps_q = DBSession.query(Game.map_id, Map.name,
            func.count()).\
            filter(Map.map_id==Game.map_id).\
            filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
            order_by(expr.desc(func.count())).\
            group_by(Game.map_id).\
            group_by(Map.name)

    if region and region != "" and region != "0":
      top_maps_q = top_maps_q.filter(Server.region==region).filter(Server.server_id==Game.server_id)
    if game_type_cd and game_type_cd != "":
      top_maps_q = top_maps_q.filter(Game.game_type_cd == game_type_cd)    

    return top_maps_q

def recent_games_q(server_id=None, map_id=None, player_id=None,
        game_type_cd=None, cutoff=None, force_player_id=False):
    '''
    Returns a SQLA query of recent game data. Parameters filter
    the results returned if they are provided. If not, it is
    assumed that results from all servers and maps is desired.

    The cutoff parameter provides a way to limit how far back to
    look when querying. Only games that happened on or after the
    cutoff (which is a datetime object) will be returned.
    '''
    pgstat_alias = aliased(PlayerGameStat, name='pgstat_alias')

    recent_games_q = DBSession.query(Game.game_id, GameType.game_type_cd,
            Game.winner, Game.start_dt, GameType.descr.label('game_type_descr'),
            Server.server_id, Server.name.label('server_name'), Map.map_id,
            Map.name.label('map_name'), PlayerGameStat.player_id,
            PlayerGameStat.nick, PlayerGameStat.rank, PlayerGameStat.team,
            PlayerGameStat.elo_delta).\
            filter(Game.server_id==Server.server_id).\
            filter(Game.map_id==Map.map_id).\
            filter(Game.game_id==PlayerGameStat.game_id).\
            filter(Game.game_type_cd==GameType.game_type_cd).\
            order_by(expr.desc(Game.game_id))

    # the various filters provided get tacked on to the query
    if server_id is not None:
        recent_games_q = recent_games_q.\
            filter(Server.server_id==server_id)

    if map_id is not None:
        recent_games_q = recent_games_q.\
            filter(Map.map_id==map_id)

    # Note: force_player_id makes the pgstat row returned be from the
    # specified player_id. Otherwise it will just look for a game
    # *having* that player_id, but returning the #1 player's pgstat row
    if player_id is not None:
        if force_player_id:
            recent_games_q = recent_games_q.\
                filter(PlayerGameStat.player_id==player_id)
        else:
            recent_games_q = recent_games_q.\
                filter(PlayerGameStat.scoreboardpos==1).\
                filter(Game.game_id==pgstat_alias.game_id).\
                filter(pgstat_alias.player_id==player_id)
    else:
        recent_games_q = recent_games_q.\
            filter(PlayerGameStat.scoreboardpos==1)

    if game_type_cd is not None:
        recent_games_q = recent_games_q.\
            filter(Game.game_type_cd==game_type_cd.lower())

    if cutoff is not None:
        right_now = datetime.utcnow()
        recent_games_q = recent_games_q.\
            filter(expr.between(Game.create_dt, cutoff, right_now))

    return recent_games_q

def get_unavailable_resources(start, end):
    """Get all resources that are  booked in the given time period.
    """
    assert(type(start) == datetime.datetime)
    assert(type(end) == datetime.datetime)
    session = Session()
    b_filter = sql.or_(
        sql.between(schema.sitting.c.start_date, start, end), 
        sql.between(schema.sitting.c.end_date, start, end),
        sql.between(start, 
            schema.sitting.c.start_date, schema.sitting.c.end_date),
        sql.between(end, 
            schema.sitting.c.start_date, schema.sitting.c.end_date)
    )
    query = session.query(BookedResources).filter(b_filter)
    resources = query.all()
    return resources

    def detalleNombre(self, dia, nombre):

        inicio = datetime(dia.year, dia.month, dia.day, 0, 0)
        fin = datetime(dia.year, dia.month, dia.day, 23, 59)

        return dict(recibos=model.Recibo.query.filter(
            model.Recibo.cliente.like(u"%{0}%".format(nombre))
        ).filter(between(model.Recibo.dia, inicio, fin)).all())

def iplatlondel():
# If only google actually supported sessions! Darn them!
#    if 'oldolddatetime' in session:
#        serverhits=m.ServerHit.query.filter(between(m.ServerHit.insdatetime,session.get('oldolddatetime'),session.get('olddatetime'))).limit(4000).all()
    serverhits=m.ServerHit.query.filter(between(m.ServerHit.insdatetime,datetime.datetime.now()-datetime.timedelta(seconds=8),datetime.datetime.now()-datetime.timedelta(seconds=2))).all()
    for row in serverhits:
        yield {
            'del': '<Placemark targetId="A'+str(row.id)+'"></Placemark>'
        }

    def dia(self, dia):

        """Muestra los recibos de un dia"""

        inicio = datetime(dia.year, dia.month, dia.day, 0, 0)
        fin = datetime(dia.year, dia.month, dia.day, 23, 59)

        return dict(recibos=model.Recibo.query.filter(
            between(model.Recibo.dia, inicio, fin)).all(),
                    dia=dia)

 def get_sittings(self):
     formatter = self.request.locale.dates.getFormatter("date", "full")
     session = Session()
     query = (
         session.query(domain.GroupSitting)
         .filter(
             sql.and_(
                 schema.sittings.c.status.in_(get_states("groupsitting", tagged=["public"])),
                 sql.between(schema.sittings.c.start_date, self.start_date, self.end_date),
             )
         )
         .order_by(schema.sittings.c.start_date)
         .options(
             eagerload("group"),
             # eagerload('sitting_type'),
             eagerload("item_schedule"),
             eagerload("item_schedule.item"),
         )
     )
     sittings = query.all()
     day = u""
     day_list = []
     s_dict = {}
     for sitting in sittings:
         sday = formatter.format(sitting.start_date)
         if sday != day:
             s_list = []
             day = sday
             if s_dict:
                 day_list.append(s_dict)
             s_dict = {}
         if sitting.group.type == "parliament":
             _url = url.set_url_context("/business/sittings/obj-%i" % (sitting.sitting_id))
         elif sitting.group.type == "committee":
             _url = url.set_url_context(
                 "/business/committees/obj-%i/sittings/obj-%i" % (sitting.group.group_id, sitting.sitting_id)
             )
         else:
             _url = "#"
         s_list.append(
             {
                 "start": sitting.start_date.strftime("%H:%M"),
                 "end": sitting.end_date.strftime("%H:%M"),
                 "type": sitting.group.type,
                 "name": sitting.group.short_name,
                 "url": _url,
                 "items": self.get_sitting_items(sitting),
             }
         )
         s_dict["day"] = day
         s_dict["sittings"] = s_list
     else:
         if s_dict:
             day_list.append(s_dict)
     return day_list

    def on_message(self, message):
        command = message.split('|')[0]
        query = '|'.join(message.split('|')[1:])
        if command == 'criterion':
            criterion = query.split('|')[0]
            value = '|'.join(query.split('|')[1:])
            if criterion == 'date':
                try:
                    value = datetime.strptime(
                        value.replace('+', ' '), '%Y-%m-%d %H:%M:%S')
                except ValueError:
                    try:
                        value = datetime.strptime('%Y-%m-%d')
                    except ValueError:
                        value = datetime.now()
                filter_ = between(Visit.date,
                                  value.date(),
                                  value.date() + timedelta(days=1))
            elif criterion in (
                    'referrer', 'asn', 'browser_name', 'site',
                    'browser_version', 'browser_name_version', 'query'):
                filter_ = getattr(Visit, criterion).ilike('%%%s%%' % value)
            else:
                filter_ = func.lower(
                    getattr(Visit, criterion)) == value.lower()

            query = (self.db
                     .query(Visit)
                     .filter(filter_))
            dialect = query.session.bind.dialect
            compiler = SQLCompiler(dialect, query.statement)
            compiler.compile()
            self.count = 0
            self.stop = 20
            self.state = 'start'
            self.execute(compiler.string, compiler.params)
        elif command == 'more':
            if self.state == 'paused':
                self.stop += 20
                self.state = 'executing'
                self.cursor.execute(
                    'FETCH FORWARD 1 FROM visit_cur;')
        elif command == '/status':
            for i, conn in enumerate(adb._pool):
                if conn.busy():
                    self.write_message(
                        'INFO|Connection %d is busy: '
                        'Executing? %s Closed? %d Status? %s (%d)' % (
                            i, conn.connection.isexecuting(),
                            conn.connection.closed,
                            conn.connection.get_transaction_status(),
                            conn.connection.get_backend_pid()))
                else:
                    self.write_message('INFO|Connection %d is free' % i)

def check_bookings(start, end, resource):
    """Return all sitting a resource is booked for in the period.
    """
    assert(type(resource) == domain.Resource)
    assert(type(start) == datetime.datetime)
    assert(type(end) == datetime.datetime)
    session = Session()
    b_filter = sql.and_(
        schema.resources.c.resource_id == resource.resource_id,
        sql.or_(
            sql.between(schema.sitting.c.start_date, start, end), 
            sql.between(schema.sitting.c.end_date, start, end),
            sql.between(start, 
                schema.sitting.c.start_date, schema.sitting.c.end_date),
            sql.between(end, 
                schema.sitting.c.start_date, schema.sitting.c.end_date)
        )
    )
    query = session.query(BookedResources).filter(b_filter)
    bookings = query.all()
    return bookings

def get_unavailable_venues(start, end, sitting=None):
    """
    get all venues that are  booked 
    in the given time period
    """
    assert type(start) == datetime.datetime
    assert type(end) == datetime.datetime
    session = Session()
    b_filter = sql.or_(
        sql.between(schema.group_sittings.c.start_date, start, end),
        sql.between(schema.group_sittings.c.end_date, start, end),
        sql.between(start, schema.group_sittings.c.start_date, schema.group_sittings.c.end_date),
        sql.between(end, schema.group_sittings.c.start_date, schema.group_sittings.c.end_date),
    )
    if sitting:
        if sitting.group_sitting_id:
            b_filter = sql.and_(b_filter, schema.group_sittings.c.group_sitting_id != sitting.group_sitting_id)
    query = session.query(BookedVenue).filter(b_filter)
    venues = query.all()
    # session.close()
    return venues

 def get_sittings(self):
     formatter = self.request.locale.dates.getFormatter('date', 'full') 
     session = Session()
     query = session.query(domain.GroupSitting).filter(
         sql.and_(
             schema.group_sittings.c.status.in_(get_states('groupsitting',
                 tagged=['public'])
             ),
             sql.between(
                 schema.group_sittings.c.start_date,
                 self.start_date,
                 self.end_date))).order_by(
                     schema.group_sittings.c.start_date).options(
                     eagerload('group'), 
                     #eagerload('sitting_type'),
                     eagerload('item_schedule'), 
                     eagerload('item_schedule.item')
         )
     sittings = query.all()
     day = u''
     day_list = []
     s_dict = {}
     for sitting in sittings:
         sday = formatter.format(sitting.start_date)
         if sday != day:
             s_list = []
             day = sday
             if s_dict:
                 day_list.append(s_dict)
             s_dict = {}
         if sitting.group.type == 'parliament':
             _url = url.set_url_context('/business/sittings/obj-%i' % (
                  sitting.group_sitting_id))
         elif sitting.group.type == 'committee':
             _url = url.set_url_context(
                 '/business/committees/obj-%i/sittings/obj-%i'
                 % (sitting.group.group_id, sitting.group_sitting_id))
         else:
             _url ='#'
         s_list.append({
             'start': sitting.start_date.strftime("%H:%M"),
             'end' : sitting.end_date.strftime("%H:%M"),
             'type' : sitting.group.type,
             'name' : sitting.group.short_name,
             'url' : _url, 
             'items' : self.get_sitting_items(sitting),
             })
         s_dict['day'] = day
         s_dict['sittings'] = s_list
     else:
         if s_dict:
             day_list.append(s_dict)
     return day_list

    def diaCasa(self, dia, casa):

        """Muestra los recibos de un dia en una casa"""

        casa = model.Casa.get(casa)
        inicio = datetime(dia.year, dia.month, dia.day, 0, 0)
        fin = datetime(dia.year, dia.month, dia.day, 23, 59)

        recibos = model.Recibo.query.filter_by(casa=casa).filter(
            between(model.Recibo.dia, inicio, fin)).all()

        return dict(recibos=recibos, dia=dia, casa=casa)

    def periodoCasa(self, inicio, fin, casa):

        """Muestra los ingresos por caja en un dia, una sucursal y una
        organización en especifico"""

        casa = model.Casa.get(casa)
        inicio = datetime(inicio.year, inicio.month, inicio.day, 0, 0)
        fin = datetime(fin.year, fin.month, fin.day, 23, 59)

        recibos = model.Recibo.query.filter_by(casa=casa).filter(
            between(model.Recibo.dia, inicio, fin)).all()

        return dict(recibos=recibos, inicio=inicio, fin=fin, casa=casa, dia=fin)