def get_missing_evaluations(user_id=None): if not user_id: return [] session = ConnectionManager.get_instance().get_session() rows = session.execute(text(""" SELECT id, name FROM candidate WHERE id NOT IN ( SELECT candidate FROM ranking WHERE evaluator = :evaluator_id) ORDER BY id """, bindparams=[bindparam('evaluator_id', user_id)])) return utilities.result_set_to_dict(rows)
def get_ranking(evaluator_id=None, candidate_id=None): if not evaluator_id or not candidate_id: raise ValueError session = ConnectionManager.get_instance().get_session() rows = session.query(Ranking).filter(Ranking.evaluator == evaluator_id, Ranking.candidate == candidate_id) try: return rows[0] except: return 999
def get_user(user_email): if not user_email: return None session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter(Evaluator.email == user_email) # session.close() try: return rows[0] except: return None
def get_evaluations(candidate_id=None, evaluator_id=None): if not candidate_id or not evaluator_id: raise ValueError("The id of the candidate or the id of the evaluator cannot be None!") session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluation) \ .filter(Evaluation.candidate == candidate_id, Evaluation.evaluator == evaluator_id) \ .order_by(Evaluation.criterion) rows_dictionary = Service.list_to_dict(lambda evaluation: evaluation.criterion, rows) session.close() return rows_dictionary
def get_user(user_email): if not user_email: return None session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter(Evaluator.email == user_email) # session.close() try: return rows[0] except: return None
def get_ranking(evaluator_id=None, candidate_id=None): if not evaluator_id or not candidate_id: raise ValueError session = ConnectionManager.get_instance().get_session() rows = session.query(Ranking).filter(Ranking.evaluator == evaluator_id, Ranking.candidate == candidate_id) try: return rows[0] except: return 999
def get_missing_evaluations(user_id=None): if not user_id: return [] session = ConnectionManager.get_instance().get_session() rows = session.execute( text(""" SELECT id, name FROM candidate WHERE id NOT IN ( SELECT candidate FROM ranking WHERE evaluator = :evaluator_id) ORDER BY id """, bindparams=[bindparam('evaluator_id', user_id)])) return utilities.result_set_to_dict(rows)
def get_candidate(candidate_id=None): if not candidate_id: raise ValueError("The id of the candidate cannot be null!") session = ConnectionManager.get_instance().get_session() rows = session.query(Candidate).filter(Candidate.id == candidate_id) # session.close() print(rows) try: return rows[0] except: return None
def get_candidate(candidate_id=None): if not candidate_id: raise ValueError("The id of the candidate cannot be null!") session = ConnectionManager.get_instance().get_session() rows = session.query(Candidate).filter(Candidate.id == candidate_id) # session.close() print(rows) try: return rows[0] except: return None
def get_progress(evaluator_id=None): if not evaluator_id: return 0 session = ConnectionManager.get_instance().get_session() evaluated = session.execute(""" SELECT COUNT(*) FROM candidate WHERE id IN ( SELECT candidate FROM ranking WHERE ranking is not null AND ranking <> 999 AND evaluator=:evaluator_id)""", {'evaluator_id': evaluator_id}).fetchone()[0] total = session.execute("""SELECT COUNT(*) FROM candidate""").fetchone()[0] return int(evaluated * 1.0 / total * 100)
def update_user(evaluator): if not evaluator: return session = ConnectionManager.get_instance().get_session() user = session.merge(evaluator) try: session.commit() except: session.rollback() raise finally: return user
def update_user(evaluator): if not evaluator: return session = ConnectionManager.get_instance().get_session() user = session.merge(evaluator) try: session.commit() except: session.rollback() raise finally: return user
def get_evaluator(evaluator_password=None, evaluator_email=None): if not evaluator_email: raise ValueError("Email is required to perform search") session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter(Evaluator.email == evaluator_email, Evaluator.password == evaluator_password) session.close() try: return rows[0] except: return None
def get_evaluator(evaluator_password=None, evaluator_email=None): if not evaluator_email: raise ValueError("Email is required to perform search") session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter( Evaluator.email == evaluator_email, Evaluator.password == evaluator_password) session.close() try: return rows[0] except: return None
def save_ranking(ranking=None): if not ranking: return session = ConnectionManager.get_instance().create_session() session.merge(ranking) try: print('Ranking:', ranking.ranking, 'evaluator', ranking.evaluator, 'candidate', ranking.candidate) session.commit() except: session.rollback() raise finally: session.close()
def get_missing_evaluators(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT name, email FROM evaluator WHERE id NOT IN ( SELECT evaluator FROM ranking WHERE ranking is not null AND ranking <> 999 GROUP BY evaluator HAVING COUNT(ranking) >= 12) AND id <> 0 """) result_list = [] for row in rows: result_list.append(dict(row)) return result_list
def get_system_ranking(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT SUM(ponderation) AS RANKING, ID, NAME FROM ( SELECT candidate AS ID, c.name AS NAME, crit.weight/100.0 * AVG(grade) as ponderation FROM evaluation e JOIN candidate c ON c.id = candidate JOIN criterion crit ON crit.id = e.criterion GROUP BY criterion, candidate, crit.weight ) GROUP BY ID, NAME ORDER BY RANKING DESC """) return utilities.result_set_to_dict(rows)
def get_system_ranking(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT SUM(ponderation) AS RANKING, ID, NAME FROM ( SELECT candidate AS ID, c.name AS NAME, crit.weight/100.0 * AVG(grade) as ponderation FROM evaluation e JOIN candidate c ON c.id = candidate JOIN criterion crit ON crit.id = e.criterion GROUP BY criterion, candidate, crit.weight ) GROUP BY ID, NAME ORDER BY RANKING DESC """) return utilities.result_set_to_dict(rows)
def get_missing_evaluators(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT name, email FROM evaluator WHERE id NOT IN ( SELECT evaluator FROM ranking WHERE ranking is not null AND ranking <> 999 GROUP BY evaluator HAVING COUNT(ranking) >= 12) AND id <> 0 """) result_list = [] for row in rows: result_list.append(dict(row)) return result_list
def get_evaluations(candidate_id=None, evaluator_id=None): if not candidate_id or not evaluator_id: raise ValueError( "The id of the candidate or the id of the evaluator cannot be None!" ) session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluation) \ .filter(Evaluation.candidate == candidate_id, Evaluation.evaluator == evaluator_id) \ .order_by(Evaluation.criterion) rows_dictionary = Service.list_to_dict( lambda evaluation: evaluation.criterion, rows) session.close() return rows_dictionary
def get_user(user_id=None): """ :rtype : int :return : Evaluator """ if not user_id: return None session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter(Evaluator.id == user_id) # session.close() try: return rows[0] except: return None
def get_ranking_result(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT c.name AS NAME, SUM(13 - r.ranking) AS RANKINGSUM, AVG(r.ranking) AS AVERAGERANKING, COUNT(r.ranking) AS COUNTRANKING FROM ranking r JOIN candidate c ON c.id = r.candidate WHERE ranking is not null AND ranking <> 999 GROUP BY c.name ORDER BY RANKINGSUM DESC """) result_list = [] for row in rows: result_list.append(dict(row)) return result_list
def get_user(user_id=None): """ :rtype : int :return : Evaluator """ if not user_id: return None session = ConnectionManager.get_instance().get_session() rows = session.query(Evaluator).filter(Evaluator.id == user_id) # session.close() try: return rows[0] except: return None
def get_ranking_result(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT c.name AS NAME, SUM(13 - r.ranking) AS RANKINGSUM, AVG(r.ranking) AS AVERAGERANKING, COUNT(r.ranking) AS COUNTRANKING FROM ranking r JOIN candidate c ON c.id = r.candidate WHERE ranking is not null AND ranking <> 999 GROUP BY c.name ORDER BY RANKINGSUM DESC """) result_list = [] for row in rows: result_list.append(dict(row)) return result_list
def save_ranking(ranking=None): if not ranking: return session = ConnectionManager.get_instance().create_session() session.merge(ranking) try: print('Ranking:', ranking.ranking, 'evaluator', ranking.evaluator, 'candidate', ranking.candidate) session.commit() except: session.rollback() raise finally: session.close()
def get_progress(evaluator_id=None): if not evaluator_id: return 0 session = ConnectionManager.get_instance().get_session() evaluated = session.execute( """ SELECT COUNT(*) FROM candidate WHERE id IN ( SELECT candidate FROM ranking WHERE ranking is not null AND ranking <> 999 AND evaluator=:evaluator_id)""", { 'evaluator_id': evaluator_id }).fetchone()[0] total = session.execute( """SELECT COUNT(*) FROM candidate""").fetchone()[0] return int(evaluated * 1.0 / total * 100)
def save_evaluations(evaluations=None): if not evaluations or not isinstance(evaluations, list): return session = ConnectionManager.get_instance().create_session() for evaluation in evaluations: session.merge(evaluation) try: session.commit() print('Commited!') except: print('Exception!!!!') session.rollback() raise finally: session.close()
def get_criteria_average(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT c.id, c.name, e.criterion, round(avg(e.grade),3) AS AVERAGE FROM evaluation e JOIN candidate c ON c.id=e.candidate GROUP BY c.id, c.name, e.criterion ORDER BY c.id, e.criterion """) result_list = utilities.result_set_to_dict(rows) result_map = dict() for a_dict in result_list: if not a_dict['NAME'] in result_map: result_map.update({a_dict['NAME']: {a_dict['CRITERION']: a_dict['AVERAGE']}}) else: result_map[a_dict['NAME']].update({a_dict['CRITERION']: a_dict['AVERAGE']}) return result_map
def save_evaluations(evaluations=None): if not evaluations or not isinstance(evaluations, list): return session = ConnectionManager.get_instance().create_session() for evaluation in evaluations: session.merge(evaluation) try: session.commit() print('Commited!') except: print('Exception!!!!') session.rollback() raise finally: session.close()
def get_comments_per_criterion(candidate_id=None): """ This function returns all the evaluator comments per criterion. :return: a dict of criteria with their respective list of ccmments. """ session = ConnectionManager.get_instance().get_session() rows = session.execute(text(""" SELECT criterion as CRITERION, comments as COMMENT FROM evaluation WHERE comments is not null and comments not like '' and candidate=:candidate_id""", bindparams=[bindparam('candidate_id', candidate_id)])) result_list = utilities.result_set_to_dict(rows) result_map = dict() for a_dict in result_list: if not a_dict['CRITERION'] in result_map: result_map.update({a_dict['CRITERION']: []}) result_map[a_dict['CRITERION']].append(a_dict['COMMENT']) return result_map
def get_comments_per_criterion(candidate_id=None): """ This function returns all the evaluator comments per criterion. :return: a dict of criteria with their respective list of ccmments. """ session = ConnectionManager.get_instance().get_session() rows = session.execute( text(""" SELECT criterion as CRITERION, comments as COMMENT FROM evaluation WHERE comments is not null and comments not like '' and candidate=:candidate_id""", bindparams=[bindparam('candidate_id', candidate_id)])) result_list = utilities.result_set_to_dict(rows) result_map = dict() for a_dict in result_list: if not a_dict['CRITERION'] in result_map: result_map.update({a_dict['CRITERION']: []}) result_map[a_dict['CRITERION']].append(a_dict['COMMENT']) return result_map
def get_criteria_average(): session = ConnectionManager.get_instance().get_session() rows = session.execute(""" SELECT c.id, c.name, e.criterion, round(avg(e.grade),3) AS AVERAGE FROM evaluation e JOIN candidate c ON c.id=e.candidate GROUP BY c.id, c.name, e.criterion ORDER BY c.id, e.criterion """) result_list = utilities.result_set_to_dict(rows) result_map = dict() for a_dict in result_list: if not a_dict['NAME'] in result_map: result_map.update( {a_dict['NAME']: { a_dict['CRITERION']: a_dict['AVERAGE'] }}) else: result_map[a_dict['NAME']].update( {a_dict['CRITERION']: a_dict['AVERAGE']}) return result_map
""" Created on Nov 29, 2013 @author: be07336 """ from sqlalchemy.ext.declarative.api import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.schema import Column, ForeignKey from sqlalchemy.types import Integer, String, NUMERIC from model.connection import ConnectionManager Base = declarative_base(bind=ConnectionManager.get_instance().get_engine()) class Candidate(Base): """ Candidate class """ __tablename__ = 'CANDIDATE' id = Column(Integer, primary_key=True) name = Column(String) email = Column(String) year = Column(String) destination = Column(String) company = Column(String) evaluations = relationship("Evaluation", primaryjoin="Candidate.id == Evaluation.candidate") others = relationship("Document", primaryjoin="and_(Candidate.id == Document.candidate,"
""" Created on Nov 29, 2013 @author: be07336 """ from sqlalchemy.ext.declarative.api import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.schema import Column, ForeignKey from sqlalchemy.types import Integer, String, NUMERIC from model.connection import ConnectionManager Base = declarative_base(bind=ConnectionManager.get_instance().get_engine()) class Candidate(Base): """ Candidate class """ __tablename__ = 'CANDIDATE' id = Column(Integer, primary_key=True) name = Column(String) email = Column(String) year = Column(String) destination = Column(String) company = Column(String) evaluations = relationship( "Evaluation", primaryjoin="Candidate.id == Evaluation.candidate")
def get_criteria(): return ConnectionManager.get_instance().get_session().query(Criterion).order_by(Criterion.id).all()
def all_candidates(): session = ConnectionManager.get_instance().get_session() result = session.query(Candidate).order_by(Candidate.id) session.close() return result
def all_candidates(): session = ConnectionManager.get_instance().get_session() result = session.query(Candidate).order_by(Candidate.id) session.close() return result
def get_criteria(): return ConnectionManager.get_instance().get_session().query( Criterion).order_by(Criterion.id).all()