def get_votes_by_entity(self):
        q0 = session.query(
            UserData.uid,
            func.count(VoteIdeaData.id).label('vote_count'))
        q0 = q0.outerjoin(UserData.votes_for_ideas)
        q0 = q0.filter(UserData.enabled == True)
        q0 = q0.group_by(UserData.uid).subquery()

        q1 = session.query(
            UserData.corporation_id.label('corporation_id'),
            UserData.direction_id.label('direction_id'),
            func.count(UserData.uid).label('user_total'),
            UserData.enabled == True)
        q1 = q1.group_by(UserData.corporation_id,
                         UserData.direction_id).subquery()

        q2 = session.query(
            UserData.corporation_id.label('corporation_id'),
            UserData.direction_id.label('direction_id'),
            func.count(UserData.uid).label('user_voted'),
            q1.c.user_total.label("total_user"),
            func.sum(q0.c.vote_count).label("total_votes"),
            ((100 * func.count(UserData.uid).label(
                'user_voted')) / q1.c.user_total).label('pourcent'))
        q2 = q2.filter(q0.c.vote_count != 0)
        q2 = q2.filter(UserData.enabled == True)
        q2 = q2.filter(UserData.uid == q0.c.uid)
        q2 = q2.filter(UserData.direction_id == q1.c.direction_id)
        q2 = q2.filter(UserData.corporation_id == q1.c.corporation_id)
        q2 = q2.group_by(UserData.corporation_id, UserData.direction_id)
        q2 = q2.order_by(desc('pourcent'))

        return q2

def get_idea_events(event_filter=None):
    # get a mixture of Comments & comments sorted by submission_date
    q1 = session.query(WFCommentData.submission_date.label('date'),
                       UserData.uid.label('user_uid'),
                       StateData.label.label('event'),
                       IdeaData.id.label('idea_id'))
    q1 = q1.join(WFCommentData.to_state,
                 WFCommentData.created_by,
                 WFCommentData.idea_wf,
                 IdeaWFContextData.idea)

    q2 = session.query(CommentData.submission_date.label('date'),
                       UserData.uid.label('user_uid'),
                       literal(u'COMMENT').label('event'),
                       IdeaData.id.label('idea_id'))
    q2 = q2.join(CommentData.created_by, CommentData.idea,
                 IdeaData.wf_context, IdeaWFContextData.state)
    # mask comments for ideas that are not in a published state
    q2 = q2.filter(StateData.label.in_(get_workflow().get_published_states()))

    q = q1.union(q2)

    # mask ideas that are currently in the dsig_basket_state
    q = q.filter(~IdeaData.id.in_(get_dsig_basket_state_ideas()))

    if event_filter:
        q = q.filter(column('event').in_(event_filter))
    q = q.order_by(desc('date'))
    return q

    def get_active_users_by_entity(self):
        q = session.query(
            UserData.corporation_id.label('corporation_id'),
            UserData.direction_id.label('direction_id'),
            func.count(UserData.uid).label('user_total'))
        q = q.filter(UserData.enabled == True)
        q = q.group_by(UserData.corporation_id,
                       UserData.direction_id).subquery()

        e = func.count(UserData.uid).label('user_count')

        q2 = session.query(
            UserData.corporation_id.label('Entite'),
            UserData.direction_id.label('Direction'),
            e.label("actifs"),
            q.c.user_total.label("total"),
            ((100 * e) / q.c.user_total).label('pourcent'))
        q2 = q2.filter(UserData.corporation_id == q.c.corporation_id)
        q2 = q2.filter(UserData.direction_id == q.c.direction_id)
        q2 = q2.filter(UserData.enabled == True)
        q2 = q2.filter(UserData.last_connection_date != None)
        q2 = q2.group_by(UserData.corporation_id, UserData.direction_id)
        q2 = q2.order_by(desc('pourcent'))

        return q2

    def run(self):
        print "OfflineSenseWorker initialized"
        # wait for stuff to initialize or the first login
        login_event.wait(300)
        if login_event.isSet():
            login_event.clear()
        time.sleep(5)

        wtime = 300
        while not stop_osw_event.isSet():
            numonline = 0
            for plr in session.query(models.Player).filter(models.Player.status > 0).all():
                if not eventlog.poke(plr.username):
                    print "poked %s - offline" % (plr.username)
                    eventlog.send_to(plr.position, "%s left" % (plr.username))
                    plr.status = 0
                else:
                    print "poked %s - still online" % (plr.username)
                    if datetime.now() - plr.lastact > timedelta(minutes=10):
                        plr.status = 0
                        eventlog.send_to(plr.position, "%s left" % (plr.username,))
                        print "  but the last activity is too long ago. set to offline."
                    else:
                        numonline += 1
            if numonline > 0:
                wtime = 300
            else:
                wtime = 3600
            print "poker waiting for %i minutes" % (wtime / 60)
            login_event.wait(wtime)
            if login_event.isSet():
                print "woken up by player login!"
                login_event.clear()
            time.sleep(5)

    def enterRoom(self, room, force = False):
        if self.player.status == 0:
            self.logout()
            return

        oldpos = self.player.position

        if isinstance(room, Room):
            newposition = room
        else:
            newposition = session.query(Room).get(room)
        
        # when we start the game, we have no old position.
        if oldpos == newposition:
            print "ignoring GameSession.enterRoom."
            send_to(self.player.position, "%s entered" % (self.player.username))
            return

        # find the door object. if it's locked, don't let us through, if it's lockable, shut it behind us.
        door = oldpos.doorTo(newposition)

        if door.locked and door.lockable():
            if force:
                door.locked = False # unlock it so that one person can get through behind us
            else:
                raise DoorLockedException
        if door.lockable() and not force:
            door.locked = True # TODO: delay this by a few seconds, so that party members can come along?

        send_to(newposition, "%s entered" % (self.player.username))
        oldpos, self.player.position = self.player.position, newposition
        send_to(oldpos, "%s left" % (self.player.username))
        self.player.activity()

 def __init__(self, room, gs):
     self.gs = gs
     if isinstance(room, basestring):
         room = session.query(Room).get(room)
     self.prev = room
     self.room = room
     self.enterRoom(room)

def _query_active_users_by_email(email):
    q = session.query(UserData.email,
                      UserData.firstname + u" " + UserData.lastname)
    q = q.filter(UserData.enabled == True)
    q = q.filter(
        func.lower(UserData.email).like(u"%" + email.lower() + u"%"))
    return q

def get_idea_published_tag(idea_id):
    tag_ids = [elt[0] for elt in
               session.query(TagData.id).outerjoin(TagData.ideas).filter(
                   IdeaData.id == idea_id)]

    result = (session.query(
        TagData.label,
        func.sum(StateData.label.in_(get_workflow().get_published_states())).label(
            'ideas_count')
    ).outerjoin(TagData.ideas))

    result = (result.outerjoin(IdeaData.wf_context)
              .outerjoin(IdeaWFContextData.state)
              .filter(TagData.id.in_(tag_ids))
              .group_by(TagData.label))

    return result

 def find_count_by_domain(self):
     return (session.query(DomainData.id, DomainData.label, func.count(IdeaData.id))
                    .join(DomainData.ideas)
                    .join(IdeaData.wf_context)
                    .join(IdeaWFContextData.state)
                    .filter(StateData.label.in_(get_workflow().get_published_states()))
                    .group_by(DomainData.id)
                    .having(func.count(IdeaData.id) > 0)
                    .order_by(DomainData.rank, DomainData.label))

def get_di_ideas_count(uid):
    return (lambda uid=uid:
            session.query(
                StateData.label.label('state'),
                func.count(IdeaWFContextData.id).label('count')
            ).outerjoin(StateData.state_for)
            .filter(IdeaWFContextData.assignated_di == UserData.query.filter(
                UserData.uid == uid).first())
            .filter(StateData.label.in_(get_workflow().get_di_basket_states()))
            .group_by(StateData.label))

def get_ideas_count_in_fi_baskets():
    fi_basket_states = get_workflow().get_fi_basket_states()
    query = session.query(UserData,
                          StateData,
                          func.count(IdeaWFContextData.id).label('count')) \
        .join(IdeaWFContextData.assignated_fi) \
        .join(IdeaWFContextData.state) \
        .filter(StateData.label.in_(fi_basket_states)) \
        .group_by(UserData.uid, StateData.id)
    return query

def get_ideas_count_by_step():
    q = session.query(
        StateData.label.label('state'),
        StepData.label.label('step'),
        func.count(IdeaWFContextData.id).label('count')
    ).outerjoin(StateData.state_for) \
        .outerjoin(StateData.step) \
        .group_by(StateData.label) \
        .order_by(StepData.rank, StateData.id)
    return q

def get_user_ideas_count(uid):
    return (lambda uid=uid:
            session.query(
                StateData.label.label('state'),
                func.count(IdeaWFContextData.id).label('count')
            ).outerjoin(StateData.state_for)
            .outerjoin(IdeaWFContextData.idea)
            .outerjoin(AuthorData)
            .join((UserData, AuthorData.user))
            .filter(UserData.uid == uid)
            .group_by(StateData.label))

 def get_guest_boards_for(cls, user_username, user_source):
     q2 = session.query(DataBoardManager.board_id)
     q2 = q2.filter(DataBoardManager.user_username == user_username)
     q2 = q2.filter(DataBoardManager.user_source == user_source)
     q = cls.query.join(DataBoardMember)
     q = q.filter(DataBoardMember.user_username == user_username)
     q = q.filter(DataBoardMember.user_source == user_source)
     q = q.filter(cls.archived == False)
     q = q.filter(~DataBoard.id.in_(q2))
     q = q.order_by(DataBoard.title)
     return q

    def cast(self, text, target=None):
        if self.gs.player.status == 0:
            self.gs.logout()
            return
        self.text(text)
        words = self.text().split()

        cards = [session.query(WordCard).get(word) for word in words]

        # make a tally for our cards so we can check if we have enough
        cdict = {}
        for card in cards:
            if card in cdict:
                cdict[card] += 1
            else:
                cdict[card] = 1

        errorcards = []

        for card, count in cdict.iteritems():
            entry = session.query(BagEntry.count).get((self.gs.player.username, card.word))
            if not entry:
                errorcards.append(card.word)
            elif entry.count < count:
                errorcards.append(card.word)

        if errorcards:
            self.errorcards = errorcards
            return

        for card, count in cdict.iteritems():
            entry = session.query(BagEntry).get((self.gs.player.username, card.word))
            entry.count -= count
            print entry.word.word, " now has count ", entry.count
            if entry.count <= 0:
                session.delete(entry)

        # update wordbag display
        self.gs.playerBox.o.wordbag.getWords()

        print ["<%s %r>" % (word.word, word.rank) for word in cards]

 def get_last_modified_boards_for(cls, user_username, user_source):
     q2 = session.query(DataHistory.board_id.distinct())
     q2 = q2.filter(DataHistory.user_username == user_username)
     q2 = q2.filter(DataHistory.user_source == user_source)
     q2 = q2.order_by(DataHistory.when.desc())
     q2 = q2.limit(5)
     q = cls.query.distinct().join(DataBoardMember)
     q = q.filter(DataBoardMember.user_username == user_username)
     q = q.filter(DataBoardMember.user_source == user_source)
     q = q.filter(DataBoard.id.in_(q2))
     q = q.filter(cls.archived == False)
     return q

    def get_latecomer_fi(self, n_days=7):
        n_days_ago = datetime.now() - timedelta(days=n_days)
        query = session.query(IdeaWFContextData,
                              func.max(WFCommentData.submission_date)) \
            .join(WFCommentData.idea_wf) \
            .join(IdeaWFContextData.state) \
            .filter(StateData.label == get_workflow().WFStates.FI_NORMALIZE_STATE) \
            .group_by(IdeaWFContextData.id)

        # there's at least one idea in FI_NORMALIZE_STATE that is older than n_days_ago
        return set(wfcontext.assignated_fi_uid for wfcontext, date in query if
                   date < n_days_ago)

    def map_cache_path(self, room, frm, typ):
        """generate a path for a cache image from the current room, where we come frm and what typ of file we want (alternatively put %s in typ)"""
        
        # scan for lockable doors from the room we are currently in
        locks = "_"

        doors = session.query(Room).get(room).doorobjs
        for door in doors:
            locks += "l" if door.locked else "o" if door.lockable() else ""

        if locks == "_": locks = ""

        return pkg_resources.resource_filename("quest", "../cache/%s_%s%s.%s" % (room, frm.name, locks, typ))

def get_ideas_count_by_di():
    WFStates = get_workflow().WFStates
    sub_query = (IdeaWFContextData.query.outerjoin(IdeaWFContextData.state)
                 .filter(StateData.label == WFStates.DI_APPRAISAL_STATE)
                 .subquery())

    return session.query(
        UserData,
        func.count(sub_query.c.id)
    ).filter(UserData.enabled == True) \
        .outerjoin((sub_query, sub_query.c.assignated_di_uid == UserData.uid)) \
        .join(RoleData) \
        .filter(RoleData.type == RoleType.Developer).group_by(UserData.uid)

    def startGame(self, player):
        self.player =  session.query(PlayerModel).get(player)
        self.player.activity()

        self.playerBox = component.Component(Player(self.player, self))
        self.roomDisplay = component.Component(RoomDisplay(self.player.position, self))
        self.spellInput = component.Component(SpellInput(self))

        self.eventlog = component.Component(Log(self.player.username))

        self.model("game")

        del self.loginManager