def best_by_user_alias(cls,alias): from quizsmith.app.models import TestsResults from quizsmith.app.utilities import Seconds2Str tests = DBSession.query(Tests,func.count(Tests.category)).filter(Tests.alias==alias).group_by(Tests.category).all() results = [] for test in tests: best_duration = DBSession.query(Tests).filter(Tests.alias==alias).filter(Tests.category==test[0].category).filter(Tests.time_spent > 0).order_by('time_spent asc').first().time_spent best_scores = DBSession.query(Tests).filter(Tests.alias==alias).filter(Tests.category==test[0].category).order_by('total_competitive desc').first() last_played = DBSession.query(Tests).filter(Tests.alias==alias).filter(Tests.category==test[0].category).order_by('created desc').first().created results.append({'Test':test[0], 'best_duration':Seconds2Str(best_duration), 'best_percentage':round(best_scores.percentage,2), 'best_competitive':int(best_scores.total_competitive), 'Count':test[1], 'last_played': last_played.strftime('%m/%d/%Y %I:%M %p')}) return results
def get_groups(self,field=None): from quizsmith.app.models import Groups if field: z = zip(*DBSession.query(getattr(Groups,field)).join(Users.groups).filter(Users.id==self.id).all()) if z: return list(z.pop()) return [] return DBSession.query(Groups).join(Users.groups).filter(Users.id==self.id).all()
def get(cls,name,default=None): try: prop = DBSession.query(Properties).filter(Properties.prop_name==name).first().prop_value if prop: return prop return default except: return default
def run(self): category = self.request.params.get('category','missing') attempts = {} #'THE_USER_NAME_HERE':1 questions = [] tests = DBSession.query(Tests).filter(Tests.category==category).filter(Tests.created>=self.start).filter(Tests.created<=self.end).order_by('created asc') if not self.include_incompleted: tests = tests.filter(Tests.completed==1) tests = tests.all() data = [ {'Attempt':'1st Attempt', 'Score':0, 'Of':0}, {'Attempt':'2nd Attempt', 'Score':0, 'Of':0}, {'Attempt':'3rd Attempt', 'Score':0, 'Of':0}, {'Attempt':'4th Attempt', 'Score':0, 'Of':0}, {'Attempt':'5th Attempt', 'Score':0, 'Of':0} ] for test in tests: if not test.alias in attempts: attempts[test.alias] = 0 else: attempts[test.alias] += 1 outof = DBSession.query(TestsResults).filter(TestsResults.tests_id==test.id).count() percent = test.total_percentage / outof if attempts[test.alias] < 5: data[attempts[test.alias]]['Score'] += percent data[attempts[test.alias]]['Of'] += 1 for i in range(5): if data[i]['Of'] > 0: data[i]['Score'] = float(data[i]['Score'] / data[i]['Of']) data[i]['Attempt'] += ' : ' + str(data[i]['Of']) + ' users ' self.response['dataset'] = data return self.response
def edit_delete(self): id = self.request.matchdict['id'] classname = str(self.request.matchdict['type']) back = self.request.params.get('back',None) if back == None or not back.startswith(self.request.application_url): return HTTPFound(location=self.request.application_url) type = Import('quizsmith.app.models',str(classname)) obj = DBSession.query(type).filter(type.id==id).first() if obj: DBSession.delete(obj) DBSession.flush() transaction.commit() # make it so number one return HTTPFound(location=self.request.params['back'])
def get_all_ranking(cls,test): sq = DBSession.query(Tests).filter(Tests.completed==1).filter(Tests.created>=LeaderBoard.archive_date()).order_by('total_competitive desc').subquery() results = DBSession.query(Tests).select_from(sq).filter(Tests.total_competitive > test.total_competitive).group_by('alias').all() data = Result2Dict(test) data['ranking'] = len(results) + 1 #prevent zero rank return data
def all_counts(cls,by='id',sort='asc',limit=1000,offset=0): return DBSession.query(Tests).filter(Tests.completed==1).filter(Tests.created>=LeaderBoard.archive_date()).group_by('alias').count()
def all_categories(cls,by='id',sort='asc',limit=1000,offset=0): sq = DBSession.query(Tests).filter(Tests.completed==1).filter(Tests.created>=LeaderBoard.archive_date()).order_by('total_competitive desc').subquery() sq2 = DBSession.query(Tests).select_from(sq).group_by('alias').subquery() return DBSession.query(Tests).select_from(sq2).order_by(by + ' ' + sort).limit(limit).offset(offset).all()
def categories(cls): return DBSession.query(Tests).group_by('category').order_by('category asc')
def counts(cls,category): return DBSession.query(Tests).filter(Tests.category==category).filter(Tests.completed==1).filter(Tests.created>=LeaderBoard.archive_date()).group_by('alias').count()
def factory(cls): aclist = [(Allow,Everyone,cls.ANONYMOUS),(Allow,Authenticated,cls.AUTHENTICATED),] for group in DBSession.query(Groups).all(): aclist.append((Allow, group.name, group.permissions())) return aclist
def groupfinder(userid, request): if Users.by(userid).first(): results = DBSession.query(Users,Groups).filter(Users.id == userid).join(Users.groups).all() return [result.Groups.name for result in results] return []
def get_transition_out(self): from quizsmith.app.models import Transitions return DBSession.query(Transitions).join(Categories.transitions_out).filter(Categories.id==self.id).first()
def all_properties(cls): results = DBSession.query(Properties).all() properties = {} for result in results: properties[result.prop_name] = result.prop_value return properties
def run(self): category = self.request.params.get('category','missing') attempts = {} # { 'KEY_USER_NAME' : INT_ATTEMPT_COUNT } questions = [] tests = DBSession.query(Tests).filter(Tests.category==category).filter(Tests.created>=self.start).filter(Tests.created<=self.end).order_by('created asc') if not self.include_incompleted: tests = tests.filter(Tests.completed==1) tests = tests.all() for test in tests: if not test.alias in attempts: attempts[test.alias] = 1 else: attempts[test.alias] += 1 results = DBSession.query(TestsResults).filter(TestsResults.tests_id==test.id).all() for result in results: data = None for ds in questions: if ds.equals(result.question): data = ds if data == None: data = self.Struct(result.question, result.question_sets_id) data.wrong_multiplier = test.max_wrong_answer_allowed questions.append(data) if attempts[test.alias] == 1: if result.wrong_attempts != 0: if result.wrong_attempts == test.max_wrong_answer_allowed: data.attempts_one_wrong += 1 else: data.attempts_one_partial += 1 data.wrongly_answered(result.answer_choices, data.answer_one_choices) else: data.attempts_one_correct += 1 if attempts[test.alias] == 2: if result.wrong_attempts != 0: if result.wrong_attempts == test.max_wrong_answer_allowed: data.attempts_two_wrong += 1 else: data.attempts_two_partial += 1 data.wrongly_answered(result.answer_choices, data.answer_two_choices) else: data.attempts_two_correct += 1 if attempts[test.alias] == 3: if result.wrong_attempts != 0: if result.wrong_attempts == test.max_wrong_answer_allowed: data.attempts_three_wrong += 1 else: data.attempts_three_partial += 1 data.wrongly_answered(result.answer_choices, data.answer_three_choices) else: data.attempts_three_correct += 1 for question in questions: data = {} data['question'] = question.question data['question_sets_id'] = str(question.question_sets_id) data['wrong_multiplier'] = question.wrong_multiplier data['one_percent'] = self.percentage(float(question.attempts_one_wrong) / float(question.attempts_one_wrong + question.attempts_one_correct)) data['one_wrong'] = question.attempts_one_wrong data['one_partial'] = question.attempts_one_partial data['one_correct'] = question.attempts_one_correct data['one_answers'] = filter(lambda x: x['wrong'] != 0, sorted(question.answer_one_choices, key=itemgetter('wrong'), reverse=True)) data['two_percent'] = self.percentage(float(question.attempts_two_wrong) / float(question.attempts_two_wrong + question.attempts_two_correct)) data['two_wrong'] = question.attempts_two_wrong data['two_partial'] = question.attempts_two_partial data['two_correct'] = question.attempts_two_correct data['two_answers'] = filter(lambda x: x['wrong'] != 0, sorted(question.answer_two_choices, key=itemgetter('wrong'), reverse=True)) data['three_percent'] = self.percentage(float(question.attempts_three_wrong) / float(question.attempts_three_wrong + question.attempts_three_correct)) data['three_wrong'] = question.attempts_three_wrong data['three_partial'] = question.attempts_three_partial data['three_correct'] = question.attempts_three_correct data['three_answers'] = filter(lambda x: x['wrong'] != 0, sorted(question.answer_three_choices, key=itemgetter('wrong'), reverse=True)) self.response['rows'].append(data) return self.response
def groups_auto_assigned_by_category(self,category_id): from quizsmith.app.models import Categories return DBSession.query(Groups).join(Groups.categories).filter(Categories.id==category_id).all()