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