Ejemplo n.º 1
0
    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
Ejemplo n.º 2
0
    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
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
    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
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
    def get_comments_by_entity(self):
        # Count distinct commenters for each direction
        commenters_by_direction = {}
        q = session.query(
            UserData.corporation_id, UserData.direction_id,
            func.count(UserData.uid.distinct())).join(
                UserData.comments).filter(UserData.enabled == True).group_by(
                    UserData.corporation_id, UserData.direction_id)

        for corpo_id, dir_id, commenters_count in q:
            commenters_by_direction[(corpo_id, dir_id)] = commenters_count

        # Count comments for each direction
        comments_by_direction = {}
        q = session.query(
            UserData.corporation_id, UserData.direction_id,
            func.count(CommentData.id)).join(UserData.comments).filter(
                UserData.enabled == True).group_by(UserData.corporation_id,
                                                   UserData.direction_id)

        for corpo_id, dir_id, comments_count in q:
            comments_by_direction[(corpo_id, dir_id)] = comments_count

        # Count users in each direction
        count_by_direction = {}
        q = session.query(UserData.corporation_id, UserData.direction_id,
                          func.count(UserData.uid)).filter(
                              UserData.enabled == True).group_by(
                                  UserData.corporation_id,
                                  UserData.direction_id)
        for corpo_id, dir_id, user_count in q:
            count_by_direction[(corpo_id, dir_id)] = user_count

        r = []
        for (corporation_id, direction_id
             ), comments_count in comments_by_direction.iteritems():
            if direction_id:
                commenters = commenters_by_direction.get(
                    (corporation_id, direction_id), 0)
                comments = comments_by_direction.get(
                    (corporation_id, direction_id), 0)
                count = count_by_direction.get((corporation_id, direction_id),
                                               0)
                r.append((corporation_id, direction_id, commenters, comments,
                          count, 100.0 * commenters / count))

        # Results sorted by descending ratio
        return sorted(r, key=lambda row: row[5], reverse=True)
Ejemplo n.º 7
0
    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)
Ejemplo n.º 8
0
 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)
Ejemplo n.º 9
0
 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)
Ejemplo n.º 10
0
    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()
Ejemplo n.º 11
0
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
Ejemplo n.º 12
0
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))
Ejemplo n.º 13
0
    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]
        score = reduce(lambda scr, wrd: scr + wrd.rank, cards, 0)
        print score
        
        self.text("")

        self.gs.player.activity()
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
    def __init__(self):
        # select a few random words from the word cards
        words = session.query(WordCard).order_by(random()).limit(6).all()

        self.correct = words[0]
        self.wrongs = words[1:]

        self.words = words
        shuffle(self.words)
Ejemplo n.º 16
0
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
Ejemplo n.º 17
0
    def __init__(self):
        # select a few random words from the word cards
        words = session.query(WordCard).order_by(random()).limit(6).all()

        self.correct = words[0]
        self.wrongs = words[1:]

        self.words = words
        shuffle(self.words)
Ejemplo n.º 18
0
 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))
Ejemplo n.º 19
0
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))
Ejemplo n.º 20
0
 def find_count_by_domain(self):
     return (session.query(DomainData.id, DomainData.i18n_label_column(), 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.i18n_label_column()))
Ejemplo n.º 21
0
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
Ejemplo n.º 22
0
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))
Ejemplo n.º 23
0
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
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
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
Ejemplo n.º 26
0
 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
Ejemplo n.º 27
0
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))
Ejemplo n.º 28
0
    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]
Ejemplo n.º 29
0
    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)
Ejemplo n.º 30
0
    def sort_by_last_wf_comment_date(self, q):
        last_comments_dates = (session.query(
            IdeaData.id.label('idea_id'),
            func.max(WFCommentData.submission_date).label(
                'last_comment_date')).join(IdeaData.wf_context).join(
                    IdeaWFContextData.comments).group_by(
                        IdeaData.id).subquery())

        q = (q.filter(IdeaData.id == last_comments_dates.c.idea_id).order_by(
            desc(last_comments_dates.c.last_comment_date)))

        return q
Ejemplo n.º 31
0
 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
Ejemplo n.º 32
0
    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)
Ejemplo n.º 33
0
 def query(limit=limit):
     q = session.query(TagData.label,
                       func.count(IdeaData.id).label('ideas_count'))
     q = q.outerjoin(TagData.ideas)
     q = q.outerjoin(IdeaData.wf_context)
     q = q.outerjoin(IdeaWFContextData.state)
     q = q.filter(StateData.label.in_(
         get_workflow().get_published_states()))
     q = q.order_by(desc('ideas_count'))
     q = q.group_by(TagData.label)
     q = q.limit(limit)
     return q
Ejemplo n.º 34
0
    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))
Ejemplo n.º 35
0
    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
Ejemplo n.º 36
0
    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
Ejemplo n.º 37
0
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)
Ejemplo n.º 38
0
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)
Ejemplo n.º 39
0
 def query(limit=limit):
     q = session.query(
         TagData.label,
         func.count(IdeaData.id).label('ideas_count'))
     q = q.outerjoin(TagData.ideas)
     q = q.outerjoin(IdeaData.wf_context)
     q = q.outerjoin(IdeaWFContextData.state)
     q = q.filter(
         StateData.label.in_(get_workflow().get_published_states()))
     q = q.order_by(desc('ideas_count'))
     q = q.group_by(TagData.label)
     q = q.limit(limit)
     return q
Ejemplo n.º 40
0
    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))
Ejemplo n.º 41
0
    def sort_by_last_wf_comment_date(self, q):
        last_comments_dates = (session.query(IdeaData.id.label('idea_id'),
                                             func.max(
                                                 WFCommentData.submission_date).label(
                                                 'last_comment_date'))
                               .join(IdeaData.wf_context)
                               .join(IdeaWFContextData.comments)
                               .group_by(IdeaData.id)
                               .subquery())

        q = (q.filter(IdeaData.id == last_comments_dates.c.idea_id)
             .order_by(desc(last_comments_dates.c.last_comment_date)))

        return q
Ejemplo n.º 42
0
    def _query(self):
        query = session.query(ImprovementData.id).order_by(desc(ImprovementData.id))

        if not security.has_permissions('view_masked_suggestions', self):
            query = query.filter(ImprovementData.visible == True)

        # filter by state according to the selected tab
        if self.selected_tab() == 'running':
            query = query.filter(ImprovementData.state == ImprovementState.RUNNING)
        elif self.selected_tab() == 'treated':
            query = query.filter(ImprovementData.state == ImprovementState.TREATED)
        elif self.selected_tab() == 'rejected':
            query = query.filter(ImprovementData.state == ImprovementState.REJECTED)

        return query
Ejemplo n.º 43
0
    def get_ideas_count_by_domain_state(self):
        workflow = get_workflow()
        q = session.query(DomainData.id, DomainData.i18n_label_column(),
                          func.count(IdeaData.id), StateData.label)
        q = q.outerjoin(DomainData.ideas).outerjoin(IdeaData.wf_context)
        q = q.outerjoin(IdeaWFContextData.state)
        q = q.filter(
            StateData.label.in_(workflow.get_approved_states() +
                                workflow.get_refused_states()))
        q = q.group_by(DomainData.id, StateData.label)

        res = collections.defaultdict(lambda: [0, 0])
        for domain_id, domain_label, count, state in q:
            if state == workflow.WFStates.DI_APPROVED_STATE:
                res[domain_label][0] = int(count)
            else:
                res[domain_label][1] = int(count)
        return res
Ejemplo n.º 44
0
 def get_ideas_count_day_by_day(self, from_date):
     # count ideas day by day (not draft) since the from_date
     date = IdeaData.submission_date.label('date')
     db_type = urlparse(database._engines.keys()[0]).scheme
     if db_type == 'sqlite':
         formatted_date = func.strftime(
             '%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
     else:
         formatted_date = func.date_format(
             IdeaData.submission_date, '%d/%m/%y').label('formatted_date')
     q = (session.query(formatted_date, date,
                        func.count(IdeaData.id).label('count'))
          .outerjoin(IdeaData.wf_context)
          .outerjoin(IdeaWFContextData.state)
          .filter(StateData.label.in_(get_workflow().get_workflow_states()))
          .filter(IdeaData.submission_date >= from_date)
          .group_by(formatted_date)
          .order_by(formatted_date))
     return q
Ejemplo n.º 45
0
 def get_ideas_count_day_by_day(self, from_date):
     # count ideas day by day (not draft) since the from_date
     date = IdeaData.submission_date.label('date')
     db_type = urlparse(database._engines.keys()[0]).scheme
     if db_type == 'sqlite':
         formatted_date = func.strftime(
             '%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
     else:
         formatted_date = func.date_format(
             IdeaData.submission_date, '%d/%m/%y').label('formatted_date')
     q = (session.query(
         formatted_date, date,
         func.count(IdeaData.id).label('count')).outerjoin(
             IdeaData.wf_context).outerjoin(IdeaWFContextData.state).filter(
                 StateData.label.in_(
                     get_workflow().get_workflow_states())).filter(
                         IdeaData.submission_date >= from_date).group_by(
                             formatted_date).order_by(formatted_date))
     return q
Ejemplo n.º 46
0
def search_users_fulltext(search_string, limit=None):
    # Filter enabled users
    q = session.query(UserData.email,
                      UserData.lastname + u" " + UserData.firstname) \
        .filter(UserData.enabled == True) \
        .order_by(UserData.lastname)

    search_string = search_string.strip()

    # Splitting words. We assert each word must be in the lastname or firstname
    name_clauses = []
    for s in search_string.split(' '):
        name_clauses.append(or_(UserData.lastname.like('%%%s%%' % s),
                                UserData.firstname.like('%%%s%%' % s)))

    q = q.filter(or_(UserData.email.like('%%%s%%' % search_string),
                     and_(*name_clauses)))

    return q.limit(limit) if limit else q
Ejemplo n.º 47
0
    def enterRoom(self, room, binding=None):
        try:
            prevroom = self.room
        except:
            prevroom = room
        if isinstance(room, Room):
            nextroom = room
        else:
            nextroom = session.query(Room).get(room)

        try:
            self.gs.enterRoom(nextroom)
        except DoorLockedException, e:
            success = binding.call(UnlockChallenge())
            if success:
                print "yay"
                self.gs.enterRoom(nextroom, force=True)
            else:
                print "nay :("
                return
Ejemplo n.º 48
0
    def get_ideas_count_by_domain_state(self):
        workflow = get_workflow()
        q = session.query(
            DomainData.id,
            DomainData.i18n_label_column(),
            func.count(IdeaData.id),
            StateData.label)
        q = q.outerjoin(DomainData.ideas).outerjoin(IdeaData.wf_context)
        q = q.outerjoin(IdeaWFContextData.state)
        q = q.filter(StateData.label.in_(workflow.get_approved_states() +
                                         workflow.get_refused_states()))
        q = q.group_by(DomainData.id, StateData.label)

        res = collections.defaultdict(lambda: [0, 0])
        for domain_id, domain_label, count, state in q:
            if state == workflow.WFStates.DI_APPROVED_STATE:
                res[domain_label][0] = int(count)
            else:
                res[domain_label][1] = int(count)
        return res
Ejemplo n.º 49
0
 def enterRoom(self, room, binding = None):
     try:
         prevroom = self.room
     except:
         prevroom = room
     if isinstance(room, Room):
         nextroom = room
     else:
         nextroom = session.query(Room).get(room)
     
     try:
         self.gs.enterRoom(nextroom)
     except DoorLockedException, e:
         success = binding.call(UnlockChallenge())
         if success:
             print "yay"
             self.gs.enterRoom(nextroom, force=True)
         else:
             print "nay :("
             return
Ejemplo n.º 50
0
def search_users_fulltext(search_string, limit=None):
    # Filter enabled users
    q = session.query(UserData.email,
                      UserData.lastname + u" " + UserData.firstname) \
        .filter(UserData.enabled == True) \
        .order_by(UserData.lastname)

    search_string = search_string.strip()

    # Splitting words. We assert each word must be in the lastname or firstname
    name_clauses = []
    for s in search_string.split(' '):
        name_clauses.append(
            or_(UserData.lastname.like('%%%s%%' % s),
                UserData.firstname.like('%%%s%%' % s)))

    q = q.filter(
        or_(UserData.email.like('%%%s%%' % search_string),
            and_(*name_clauses)))

    return q.limit(limit) if limit else q
Ejemplo n.º 51
0
def get_all_ideas_unordered():
    """
    Returns an iterator on all ideas
    """
    q = (session.query(
        IdeaData.id.label('id'),
        IdeaWFContextData.publication_date.label('publication_date'),
        IdeaWFContextData.recommendation_date.label('recommendation_date'),
        StateData.label.label('state'), DomainData.id.label('domain_id'),
        IdeaData.total_readers.label('total_readers'),
        IdeaData.total_comments.label('total_comments'),
        IdeaData.total_votes.label('total_votes'),
        IdeaData.title.label('title'),
        IdeaData.description.label('description'),
        IdeaData.submission_date.label('submission_date'),
        IdeaData.challenge_id.label('challenge_id')))

    q = (q.join(IdeaData.wf_context).join(IdeaData.domain).join(
        IdeaWFContextData.state).group_by(IdeaData.id))

    return q
Ejemplo n.º 52
0
 def get_ideas_count_by_domain_state(self):
     workflow = get_workflow()
     q = session.query(
         DomainData.id,
         DomainData.label,
         func.count(IdeaData.id).label('count'),
         StateData.label.label('state'))
     q = q.outerjoin(DomainData.ideas).outerjoin(IdeaData.wf_context)
     q = q.outerjoin(IdeaWFContextData.state)
     q = q.filter(StateData.label.in_(workflow.get_approved_states() +
                                      workflow.get_refused_states()))
     q = q.group_by(DomainData.id, StateData.label)
     res = {}
     for elt in q:
         if elt.label not in res:
             res[elt.label] = [0, 0]
         if elt.state == workflow.WFStates.DI_APPROVED_STATE:
             res[elt.label][0] = int(elt.count)
         else:
             res[elt.label][1] = int(elt.count)
     return res
Ejemplo n.º 53
0
    def get_connection_count_day_by_day(self, from_date, platform=None):
        # count first connection day by day since the from_date
        date = PointData.date.label('date')
        db_type = urlparse(database._engines.keys()[0]).scheme
        if db_type == 'sqlite':
            formatted_date = func.strftime(
                '%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
        else:
            formatted_date = func.date_format(
                IdeaData.submission_date, '%d/%m/%y').label('formatted_date')

        q = session.query(date, formatted_date,
                          func.count(PointData.id).label('count'))
        q = q.filter(
            PointData.label == PointCategory.FIRST_CONNECTION_OF_THE_DAY)
        q = q.filter(PointData.date >= from_date)
        q = q.group_by(formatted_date)
        q = q.order_by(formatted_date)

        if platform:
            q = q.filter(PointData.subject_id == platform)
        return q
Ejemplo n.º 54
0
    def get_connection_count_day_by_day(self, from_date, platform=None):
        # count first connection day by day since the from_date
        date = PointData.date.label('date')
        db_type = urlparse(database._engines.keys()[0]).scheme
        if db_type == 'sqlite':
            formatted_date = func.strftime(
                '%d/%m/%Y', IdeaData.submission_date).label('formatted_date')
        else:
            formatted_date = func.date_format(
                IdeaData.submission_date, '%d/%m/%y').label('formatted_date')

        q = session.query(date,
                          formatted_date,
                          func.count(PointData.id).label('count'))
        q = q.filter(
            PointData.label == PointCategory.FIRST_CONNECTION_OF_THE_DAY)
        q = q.filter(PointData.date >= from_date)
        q = q.group_by(formatted_date)
        q = q.order_by(formatted_date)

        if platform:
            q = q.filter(PointData.subject_id == platform)
        return q
Ejemplo n.º 55
0
    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)