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_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 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)
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 __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 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 _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_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 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()
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 __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)
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 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()))
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_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_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 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 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_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 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
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 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
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 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
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 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 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 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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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)