def drop_unpopulars(db_prefix: str):
query_get_team_min = lambda team: f"""
SELECT proj_name, min_{team}, max_{team}, {team}
FROM projects
WHERE {team}!="DROPPED" AND {team}!=""
ORDER BY LENGTH({team})"""
fetch = lambda c: [t for t in c.fetchall() if t[1] < 90]
with db_access(db_prefix + SQL) as db:
c = db.cursor()
#c.execute(query_get_team_min('DS'))
#DS_tup = fetch(c)
c.execute(query_get_team_min('Web'))
Web_tup = fetch(c)
#c.execute(query_get_team_min('iOS'))
#iOS_tup = fetch(c)
#print(len(Web_tup), len(DS_tup), len(iOS_tup))i
# print(Web_tup)
#drop_DS = [t[0] for t in DS_tup if len(t[3].split(STRONG_SEP))-1 < t[1]]
drop_Web = [
t[0] for t in Web_tup if len(t[3].split(STRONG_SEP)) - 1 < t[1]
]
#drop_iOS = [t[0] for t in iOS_tup if len(t[3].split(STRONG_SEP))-1 < t[1]]
# for proj in set(drop_DS + drop_Web + drop_iOS)
for proj in drop_Web:
yield mark_dropped(proj, db_prefix)
def get_assignment(db_prefix: str) -> Dict[str, str]:
'''return a map from people to projects'''
get_people_assigned = "SELECT person_name, assigned FROM survey"
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(get_people_assigned)
X = c.fetchall()
return {t[0]: t[1] for t in X}
def get_unassigned(db_prefix: str) -> List[str]:
get_unassigned = f"SELECT person_name FROM survey WHERE assigned=\"NULL\""
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(get_unassigned)
unassigned = c.fetchall()
return unassigned
def get_rankorder(person: str, db_prefix: str) -> List[str]:
'''docstring'''
q = f"SELECT rank_order FROM survey WHERE person_name=\"{person}\""
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(q)
X = c.fetchall()[0][0]
return proj_prefs(X)
def get_person_proj_preferences_map(db_prefix: str) -> Dict[str, List[str]]:
'''docstring'''
get_people_names = "SELECT person_name, track FROM survey"
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(get_people_names)
X = c.fetchall()
return {t: get_rankorder(t[0], db_prefix=db_prefix) for t in X}
def get_surplus_pop(db_prefix: str) -> List[Tuple[str, str]]:
''' get the surplus popularity of a project '''
query_surplus_pop = "SELECT proj_name, surplus_popularity FROM projects WHERE surplus_popularity > 0"
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(query_surplus_pop)
proj_surplus_pop = c.fetchall()
#print(proj_surplus_pop)
return sorted(proj_surplus_pop, key=lambda t: int(t[1]))
def mark_dropped(proj: str, db_prefix: str):
''' updates db with unpopulars to be dropped'''
where = lambda feat: f"WHERE {feat}=\"{proj}\""
projects_update = f"UPDATE projects SET (Web, DS, iOS)=(\"DROPPED\", \"DROPPED\", \"DROPPED\") {where('proj_name')}"
survey_update = f"UPDATE survey SET assigned=\"NULL\" {where('assigned')}"
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(projects_update)
c.execute(survey_update)
pass
def get_unfriendlies(pers_name: str, db_prefix: str) -> List[str]:
''' take a person and return their listed unfriendlies '''
get_persons_unfriendlies = f"SELECT enemies FROM survey WHERE person_name=\"{pers_name}\""
with db_access(db_prefix + SQL) as db:
c = db.cursor()
c.execute(get_persons_unfriendlies)
X = c.fetchall()[0][0]
if X:
return ''.join([x.replace('"', '') for x in X]).split(',')
else:
return []
def assign_to_nth(n: int, db_prefix: str,
person_proj_preferences_map: Dict[str, List[str]]):
for key, val in person_proj_preferences_map.items():
try:
nth = val[n]
except IndexError:
continue
else:
where_proj_name_is_nth = f"WHERE proj_name=\"{nth}\""
where_person_name_is_key0 = f"WHERE person_name=\"{key[0]}\""
get_track = f"SELECT track FROM survey {where_person_name_is_key0}"
get_max_team = f"SELECT max_{key[1]} FROM projects {where_proj_name_is_nth}"
get_team_so_far = f"SELECT {key[1]} FROM projects {where_proj_name_is_nth}"
get_surplus_pop = f"SELECT surplus_popularity FROM projects {where_proj_name_is_nth}"
get_assigned = f"SELECT assigned FROM survey {where_person_name_is_key0}"
set_assigned_in_survey = f"UPDATE survey SET assigned = \"{nth}\" WHERE person_name=\"{key[0]}\""
set_team_in_projects = lambda updated_team: f"UPDATE projects SET {key[1]}=\"{updated_team}\" {where_proj_name_is_nth}"
set_surplus_pop = f"UPDATE projects SET surplus_popularity=surplus_popularity+(6-{n})/6 {where_proj_name_is_nth}"
with db_access(db_prefix + SQL) as db:
c = db.cursor()
# get track name fro msurvey
c.execute(get_track)
track = c.fetchall()[0][0]
# get max team size from projects
c.execute(get_max_team)
max_for_track = c.fetchall()[0][0]
#print(max_for_track)
# get team so far by track
c.execute(get_team_so_far)
team_so_far = c.fetchall()[0][0]
if len(team_so_far.split(STRONG_SEP)) >= int(max_for_track):
c.execute(set_surplus_pop)
elif team_so_far == 'DROPPED':
continue
else:
c.execute(get_assigned)
assigned = c.fetchall()[0][0]
if assigned:
continue
else:
new_team = team_so_far + STRONG_SEP + key[0]
c.execute(set_assigned_in_survey)
c.execute(set_team_in_projects(new_team))
pass