<commit_before># -*- coding: utf-8 -*-

import os
import falcon
import json
import random, string
from sqlalchemy import func, distinct, desc, text, or_
from sqlalchemy.exc import SQLAlchemyError

from db import session
import model
import util
import auth


class User(object):

	def on_get(self, req, resp, id):
		try:
			user = session.query(model.User).get(id)
		except SQLAlchemyError:
			session.rollback()
			raise

		if user is None:
			req.context['result'] = { 'errors': [ { 'status': '404', 'title': 'Not found', 'detail': u'Uživatel s tímto ID neexistuje.' } ] }
			resp.status = falcon.HTTP_404
			return

		try:
			req.context['result'] = { 'user': util.user.to_json(user, req.context['year_obj'], admin_data=req.context['user'].is_org()) }
		except SQLAlchemyError:
			session.rollback()
			raise


	def on_delete(self, req, resp, id):
		user = req.context['user']
		try:
			user_db = session.query(model.User).get(id)
		except SQLAlchemyError:
			session.rollback()
			raise

		if (not user.is_logged_in()) or (not user.is_admin()):
			resp.status = falcon.HTTP_400
			return

		if not user_db:
			resp.status = falcon.HTTP_404
			return

		profile = session.query(model.Profile).get(id)

		try:
			if profile: session.delete(profile)
			session.delete(user_db)
			session.commit()
		except SQLAlchemyError:
			session.rollback()
			raise
		finally:
			session.close()

		req.context['result'] = {}


class Users(object):
	def on_get(self, req, resp):
		filt = req.get_param('filter')
		sort = req.get_param('sort')
		usr = req.context['user']
		year = req.context['year_obj']

		# Tady se dela spoustu magie kvuli tomu, aby se usetrily SQL dotazy
		# Snazime se minimalizovat pocet dotazu, ktere musi byt provedeny pro kadeho uzivatele
		# a misto toho provest pouze jeden MEGA dotaz.
		# Slouzi predevsim pro rychle nacitani vysledkove listiny.

		try:
			# Skore uzivatele per modul (zahrnuje jen moduly evaluation_public)
			per_user = session.query(model.Evaluation.user.label('user'), func.max(model.Evaluation.points).label('points')).\
				filter(model.Evaluation.ok).\
				join(model.Module, model.Evaluation.module == model.Module.id).\
				join(model.Task, model.Task.id == model.Module.task).\
				filter(model.Task.evaluation_public).\
				join(model.Wave, model.Wave.id == model.Task.wave).\
				filter(model.Wave.year == req.context['year']).\
				group_by(model.Evaluation.user, model.Evaluation.module).subquery()

			# Pocet odevzdanych uloh (zahrnuje i module not evaluation_public i napriklad automaticky opravovane moduly s 0 body)
			tasks_per_user = session.query(model.Evaluation.user.label('user'), func.count(distinct(model.Task.id)).label('tasks_cnt')).\
				join(model.Module, model.Evaluation.module == model.Module.id).\
				join(model.Task, model.Task.id == model.Module.task).\
				join(model.Wave, model.Wave.id == model.Task.wave).\
				filter(model.Wave.year == req.context['year']).\
				group_by(model.Evaluation.user).subquery()

			# Ziskame vsechny uzivatele
			# Tem, kteri maji evaluations, je prirazen pocet bodu a pocet odevzdanych uloh
			# Vraci n tici: (model.User, total_score, tasks_cnt, model.Profile)
			# POZOR: outerjoin je dulezity, chceme vracet i uzivatele, kteri nemaji zadna evaluations (napriklad orgove pro seznam orgu)
			users = session.query(model.User, func.sum(per_user.c.points).label("total_score"), tasks_per_user.c.tasks_cnt.label('tasks_cnt'), model.Profile).\
				outerjoin(per_user, model.User.id == per_user.c.user).\
				outerjoin(tasks_per_user, model.User.id == tasks_per_user.c.user).\
				join(model.Profile, model.User.id == model.Profile.user_id).\
				group_by(model.User)

			if not usr.is_org(): users = users.filter(model.User.enabled)

			# Filtrovani skupin uzivatelu
			if filt == 'organisators' or filt == 'orgs':
				users = users.filter(or_(model.User.role == 'org', model.User.role == 'admin')).\
					join(model.ActiveOrg, model.ActiveOrg.org == model.User.id).\
					filter(model.ActiveOrg.year == year.id)
			elif filt == 'orgs-all':
				users = users.filter(or_(model.User.role == 'org', model.User.role == 'admin'))

			elif filt == 'part-hs':
				# Resitele zobrazujeme jen v aktualnim rocniku (pro jine neni tasks_cnt definovano)
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
				if year: users = users.filter(model.Profile.school_finish >= util.year.year_end(year))
			elif filt == 'part-other':
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
				if year: users = users.filter(model.Profile.school_finish < util.year.year_end(year))
			elif filt == 'part' or filt == 'participants':
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
			# Razeni uzivatelu
			if sort == 'score':
				users = users.filter(model.User.enabled).order_by(desc("total_score"))

			# Polozime SQL dotaz a ziskame vsechny relevantni uzivatele
			users = users.all()

			# A ted prijdou na radu hratky a achievementy a aktivnimi rocniky: seznamy patrici kazdemu uzivateli
			# Samozrejme nechceme pro kazdeho uzivatele delat zvlastni dotaz.

			# Achievementy:
			achievements = session.query(model.User.id.label('user_id'), model.Achievement.id.label('a_id')).\
				join(model.UserAchievement, model.UserAchievement.user_id == model.User.id).\
				join(model.Achievement, model.Achievement.id == model.UserAchievement.achievement_id).\<commit_msg>Show only current achievements in results.<commit_after># -*- coding: utf-8 -*-

import os
import falcon
import json
import random, string
from sqlalchemy import func, distinct, desc, text, or_
from sqlalchemy.exc import SQLAlchemyError

from db import session
import model
import util
import auth


class User(object):

	def on_get(self, req, resp, id):
		try:
			user = session.query(model.User).get(id)
		except SQLAlchemyError:
			session.rollback()
			raise

		if user is None:
			req.context['result'] = { 'errors': [ { 'status': '404', 'title': 'Not found', 'detail': u'Uživatel s tímto ID neexistuje.' } ] }
			resp.status = falcon.HTTP_404
			return

		try:
			req.context['result'] = { 'user': util.user.to_json(user, req.context['year_obj'], admin_data=req.context['user'].is_org()) }
		except SQLAlchemyError:
			session.rollback()
			raise


	def on_delete(self, req, resp, id):
		user = req.context['user']
		try:
			user_db = session.query(model.User).get(id)
		except SQLAlchemyError:
			session.rollback()
			raise

		if (not user.is_logged_in()) or (not user.is_admin()):
			resp.status = falcon.HTTP_400
			return

		if not user_db:
			resp.status = falcon.HTTP_404
			return

		profile = session.query(model.Profile).get(id)

		try:
			if profile: session.delete(profile)
			session.delete(user_db)
			session.commit()
		except SQLAlchemyError:
			session.rollback()
			raise
		finally:
			session.close()

		req.context['result'] = {}


class Users(object):
	def on_get(self, req, resp):
		filt = req.get_param('filter')
		sort = req.get_param('sort')
		usr = req.context['user']
		year = req.context['year_obj']

		# Tady se dela spoustu magie kvuli tomu, aby se usetrily SQL dotazy
		# Snazime se minimalizovat pocet dotazu, ktere musi byt provedeny pro kadeho uzivatele
		# a misto toho provest pouze jeden MEGA dotaz.
		# Slouzi predevsim pro rychle nacitani vysledkove listiny.

		try:
			# Skore uzivatele per modul (zahrnuje jen moduly evaluation_public)
			per_user = session.query(model.Evaluation.user.label('user'), func.max(model.Evaluation.points).label('points')).\
				filter(model.Evaluation.ok).\
				join(model.Module, model.Evaluation.module == model.Module.id).\
				join(model.Task, model.Task.id == model.Module.task).\
				filter(model.Task.evaluation_public).\
				join(model.Wave, model.Wave.id == model.Task.wave).\
				filter(model.Wave.year == req.context['year']).\
				group_by(model.Evaluation.user, model.Evaluation.module).subquery()

			# Pocet odevzdanych uloh (zahrnuje i module not evaluation_public i napriklad automaticky opravovane moduly s 0 body)
			tasks_per_user = session.query(model.Evaluation.user.label('user'), func.count(distinct(model.Task.id)).label('tasks_cnt')).\
				join(model.Module, model.Evaluation.module == model.Module.id).\
				join(model.Task, model.Task.id == model.Module.task).\
				join(model.Wave, model.Wave.id == model.Task.wave).\
				filter(model.Wave.year == req.context['year']).\
				group_by(model.Evaluation.user).subquery()

			# Ziskame vsechny uzivatele
			# Tem, kteri maji evaluations, je prirazen pocet bodu a pocet odevzdanych uloh
			# Vraci n tici: (model.User, total_score, tasks_cnt, model.Profile)
			# POZOR: outerjoin je dulezity, chceme vracet i uzivatele, kteri nemaji zadna evaluations (napriklad orgove pro seznam orgu)
			users = session.query(model.User, func.sum(per_user.c.points).label("total_score"), tasks_per_user.c.tasks_cnt.label('tasks_cnt'), model.Profile).\
				outerjoin(per_user, model.User.id == per_user.c.user).\
				outerjoin(tasks_per_user, model.User.id == tasks_per_user.c.user).\
				join(model.Profile, model.User.id == model.Profile.user_id).\
				group_by(model.User)

			if not usr.is_org(): users = users.filter(model.User.enabled)

			# Filtrovani skupin uzivatelu
			if filt == 'organisators' or filt == 'orgs':
				users = users.filter(or_(model.User.role == 'org', model.User.role == 'admin')).\
					join(model.ActiveOrg, model.ActiveOrg.org == model.User.id).\
					filter(model.ActiveOrg.year == year.id)
			elif filt == 'orgs-all':
				users = users.filter(or_(model.User.role == 'org', model.User.role == 'admin'))

			elif filt == 'part-hs':
				# Resitele zobrazujeme jen v aktualnim rocniku (pro jine neni tasks_cnt definovano)
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
				if year: users = users.filter(model.Profile.school_finish >= util.year.year_end(year))
			elif filt == 'part-other':
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
				if year: users = users.filter(model.Profile.school_finish < util.year.year_end(year))
			elif filt == 'part' or filt == 'participants':
				users = users.filter(model.User.role == 'participant').\
					filter(text("tasks_cnt"), text("tasks_cnt") > 0)
			# Razeni uzivatelu
			if sort == 'score':
				users = users.filter(model.User.enabled).order_by(desc("total_score"))

			# Polozime SQL dotaz a ziskame vsechny relevantni uzivatele
			users = users.all()

			# A ted prijdou na radu hratky a achievementy a aktivnimi rocniky: seznamy patrici kazdemu uzivateli
			# Samozrejme nechceme pro kazdeho uzivatele delat zvlastni dotaz.

			# Achievementy:
			achievements = session.query(model.User.id.label('user_id'), model.Achievement.id.label('a_id')).\
				join(model.UserAchievement, model.UserAchievement.user_id == model.User.id).\
				join(model.Achievement, model.Achievement.id == model.UserAchievement.achievement_id).\
				filter(or_(model.Achievement.year == year.id, model.Achievement.year == None)).\
{'fraction_non_alphanumeric': 0.14092940125111708, 'fraction_numerical': 0.0030384271671134943, 'mean_word_length': 3.987076648841355, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1151347', 'n_tokens_mistral': 4504, 'n_tokens_neox': 3900, 'n_words': 848}