def lookup_course(course_id, active_only=False, offer_nbr=1):
""" Get HTML for one course_id. In the case of cross-listed courses, give the one with offer_nbr
equal to 1 unless overridden.
"""
conn = PgConnection()
cursor = conn.cursor()
# Active courses require both the course and the discipline to be active
if active_only:
which_courses = """
and c.course_status = 'A'
and c.discipline_status = 'A'
"""
else:
which_courses = ''
cursor.execute(course_query.format(which_courses), (course_id, offer_nbr))
if cursor.rowcount == 0:
print('COURSE LOOKUP FAILED', cursor.query)
return None
if cursor.rowcount > 1:
raise Exception(
f'lookup_course() found {cursor.rowcount} courses for {course_id}:{offer_nbr}'
)
course = cursor.fetchone()
conn.close()
html = format_course(course)
return [course, html]
def course_search(search_str, include_inactive=False, debug=False):
""" Parse search string to get institution, discipline, and catalog_number, then find all matching
courses and return an array of catalog entries.
Search strings by example
qns csci * All CSCI courses at QC
* csci 101 CSCI-101 at any college
QNS01 CSCI101 CSCI 101 at QC
QNS csci-101 CSCI 100 at QC
"""
if debug:
print(f'\n*** couirse_search("{search_str}", {include_inactive})')
parts = search_str.split()
if len(parts) < 2 or len(parts) > 3:
raise ValueError('invalid search string')
institution = parts[0]
if len(parts) == 2:
discipline, catalog_number = re.match(r'^\s*([a-z]+)-?(.+)\s*$',
parts[1], re.I).groups()
else:
discipline, catalog_number = parts[1], parts[2]
catalog_number = re.match(r'^\s*(\*|[\d\.]+)\D*$', catalog_number).group(1)
if include_inactive:
status_str = "course_status = 'A' or course_status = 'I'"
else:
status_str = "course_status = 'A' and can_schedule = 'Y'"
if institution != '*':
institution_str = f"and institution ~* '{institution}'"
else:
institution_str = ''
if catalog_number != '*':
under = float(catalog_number) - 0.5
over = under + 1.0
cat_num_str = """and numeric_part(catalog_number) > {} and numeric_part(catalog_number) < {}
""".format(under, over)
else:
cat_num_str = ''
query = f"""
select course_id, offer_nbr, course_status, institution, discipline, catalog_number
from cuny_courses
where {status_str}
{institution_str}
and discipline ~* %s
{cat_num_str}
"""
conn = PgConnection()
cursor = conn.cursor()
cursor.execute(query, (discipline, ))
return_list = []
for row in cursor.fetchall():
return_list.append(
lookup_course(row.course_id, offer_nbr=row.offer_nbr)[1])
conn.close()
return json.dumps(return_list)
def rule_history(rule_key):
""" Generate HTML for the review-history of a transfer rule.
"""
conn = PgConnection()
cursor = conn.cursor()
cursor.execute(
"""
select s.description,
e.who,
e.what,
to_char(e.event_time, 'YYYY-MM-DD HH12:MI am') as event_time
from events e, review_status_bits s
where e.rule_id = (select id from transfer_rules
where source_institution = %s
and destination_institution = %s
and subject_area = %s
and group_number = %s)
and s.abbr = e.event_type
order by e.event_time desc
""", rule_key.split('-'))
history_rows = ''
if cursor.rowcount < 1:
history_rows = '<tr><td colspan="3">There is no review history for this rule</td></tr>'
else:
for event in cursor.fetchall():
what = '<div class="history-what-type">{}</div> {}'.format(
event.description, event.what)
history_rows += """
<tr>
<td>{}</td>
<td>{}</td>
<td>{}</td>
</tr>
""".format(event.event_time.replace(' 0', ' '), event.who, what)
cursor.close()
conn.close()
print(f'>>{format_rule_by_key(rule_key)[1]}<<')
result = f"""
<h2>Transfer Rule {rule_key}</h2>
<p class="instructions">{format_rule_by_key(rule_key)[1]}</p>
<h2>Review History</h2>
<table>
<tr>
<th>When</th>
<th>Who</th>
<th>What</th>
</tr>
{history_rows}
</table>
"""
return result
def _course_info(course_id_str: str):
""" Return a table telling all about a course.
"""
try:
course_id, offer_nbr = course_id_str.split(':')
offer_nbr = int(offer_nbr)
except ValueError as ve:
course_id = course_id_str.strip()
offer_nbr = 1
conn = PgConnection()
cursor = conn.cursor()
cursor.execute('select * from cuny_institutions')
colleges = {i.code: i.name for i in cursor.fetchall()}
cursor.execute(f'select * from cuny_courses where course_id = %s',
(course_id, ))
if cursor.rowcount < 1:
title = '<h1 class="error">Course not found</h1>'
result = '<p>The course you requested does not appear to match any course in CUNYfirst.</p>'
result += f'<p>{course_id}:{offer_nbr}'
else:
info = dict()
for row in cursor.fetchall():
info[int(row.offer_nbr)] = row
if offer_nbr not in info:
title = '<h1 class="error">Course not found</h1>'
result = '<p>The course you requested does not appear to match any course in CUNYfirst.</p>'
result += f'<p>{course_id}:{offer_nbr}'
else:
course = info[offer_nbr]
college = colleges[course.institution]
title = f'<h1>{college} {course.discipline} {course.catalog_number}: <em>{course.title}</em></h1>\n'
result = '<table><tr><th>Property</th><th>Value</th></tr>\n'
if cursor.rowcount > 1:
result += ('<tr><th>Cross-listed with</th><td>' + '<br>'.join([f'{info[a].discipline} '
f'{info[a].catalog_number}'
for a in info
if a != offer_nbr])
+ '</td></tr>\n')
result += f'<tr><th>Description</th><td> {course.description} </td></tr>\n'
gened_text = expand_rd(course.designation)
result += f'<tr><th>General Education</th><td> {gened_text} </td></tr>\n'
result += f'<tr><th>Transfers To</th><td> ... </td></tr>\n'
result += f'<tr><th>Transfers From</th><td> ... </td></tr>\n'
wric = 'Yes' if 'WRIC' in course.attributes else 'No'
result += f'<tr><th>Writing Intensive</th><td>{wric}</td></tr>\n'
result += '</table>'
conn.close()
return title, result
def is_bkcr(course_id):
""" Look up the course to see if it has the BKCR attribute
"""
course_id = int(course_id)
conn = PgConnection()
cursor = conn.cursor()
cursor.execute(f"""
select * from cuny_courses
where course_id = {course_id}
and attributes ~* 'BKCR'
""")
assert cursor.rowcount < 2
rowcount = cursor.rowcount
conn.close()
return rowcount > 0
def do_form_1(request, session):
"""
1. Collect source institutions, destination institutions and user's email from Form 1, and add
them to the session.
2. Generate Form 2 to select discipline(s)
"""
if DEBUG:
print(f'*** do_form_1({session})')
# do_form_1: put form 1 info (source/destination colleges; users email) into the session
# dictionary.
session['source_institutions'] = request.form.getlist('source')
session['destination_institutions'] = request.form.getlist('destination')
session['email'] = request.form.get('email')
session['remember_me'] = request.form.get('remember-me') == 'on'
# The session: does the user want her to persist?
session.permanent = session['remember_me']
# Database lookups
# ----------------
conn = PgConnection()
cursor = conn.cursor()
# The CUNY Subjects table, for getting subject descriptions from their abbreviations
cursor.execute("select * from cuny_subjects order by subject")
subject_names = {row.subject: row.subject_name for row in cursor}
# Generate table headings for source and destination institutions
sending_is_singleton = False
sending_heading = 'Sending Colleges’'
receiving_is_singleton = False
receiving_heading = 'Receiving Colleges’'
criterion = ''
if len(session['source_institutions']) == 1:
sending_is_singleton = True
criterion = 'the sending college is ' + institution_names[
session['source_institutions'][0]]
sending_heading = f"{institution_names[session['source_institutions'][0]]}’s".replace(
's’s', 's’')
if len(session['destination_institutions']) == 1:
receiving_is_singleton = True
receiving_heading = f"{institution_names[session['destination_institutions'][0]]}’s".replace(
's’s', 's’')
if sending_is_singleton:
criterion += ' and '
criterion += 'the receiving college is ' + \
institution_names[session['destination_institutions'][0]]
# Look up all {source_institution, source_discipline, cuny_subject}
# and {destination_institution, destination_discipline, cuny_subject}
# tuples for the selected source and destination institutions.
source_institution_params = ', '.join(
'%s' for i in session['source_institutions'])
q = """
select institution,
'<span title="'||discipline_name||'">'||discipline||'</span>' as discipline,
cuny_subject
from cuny_disciplines
where institution in ({})
""".format(source_institution_params)
cursor.execute(q, session['source_institutions'])
source_disciplines = cursor.fetchall()
destination_institution_params = ', '.join(
'%s' for i in session['destination_institutions'])
q = f"""
select institution,
'<span title="'||discipline_name||'">'||discipline||'</span>' as discipline,
cuny_subject
from cuny_disciplines
where institution in ({destination_institution_params})
"""
cursor.execute(q, session['destination_institutions'])
destination_disciplines = cursor.fetchall()
cursor.close()
conn.close()
# do_form_1: generate form 2
# -----------------------------------------------------------------------------------------------
# The CUNY subjects actually used by the source and destination disciplines.
cuny_subjects = set([d.cuny_subject for d in source_disciplines])
cuny_subjects |= set([d.cuny_subject for d in destination_disciplines])
cuny_subjects.discard(
'') # empty strings don't match anything in the subjects table.
cuny_subjects = sorted(cuny_subjects)
# Build selection list. For each cuny_subject found in either sending or receiving disciplines,
# list all disciplines for that subject, with checkboxes for selecting either the sending or
# receiving side.
# The user sees College: discipline(s) in the table (source_disciplines_str), and that info is
# encoded as a colon-separated list of college-discipline pairs (source_disciplines_val) as the
# value of the corresponding cbox. *** TODO *** and then parse the value in do_form_2() ***
selection_rows = ''
num_rows = 0
for cuny_subject in cuny_subjects:
# Sending College(s)’ Disciplines
# Both the college and discipline names will be displayed for each cuny_subject, unless there
# is only one college involved ("singleton"), in which case only the discipline name is shown.
source_disciplines_str = ''
source_disciplines_val = ''
source_disciplines_set = set()
for discipline in source_disciplines:
if discipline.cuny_subject == cuny_subject:
if sending_is_singleton:
source_disciplines_set.add(discipline.discipline)
else:
source_disciplines_set.add(
(discipline.institution, discipline.discipline))
source_disciplines_set = sorted(source_disciplines_set)
if sending_is_singleton:
if len(source_disciplines_set) > 1:
source_disciplines_str = '<div>' + '</div><div>'.join(
source_disciplines_set) + '</div>'
else:
source_disciplines_str = ''.join(source_disciplines_set)
else:
colleges = {}
for discipline in source_disciplines_set:
if discipline[0] not in colleges.keys():
colleges[discipline[0]] = []
colleges[discipline[0]].append(discipline[1])
for college in colleges:
source_disciplines_str += '<div>{}: <em>{}</em></div>'.format(
institution_names[college], ', '.join(colleges[college]))
# Receiving College Disciplines
destination_disciplines_str = ''
destination_disciplines_set = set()
for discipline in destination_disciplines:
if discipline.cuny_subject == cuny_subject:
if receiving_is_singleton:
destination_disciplines_set.add(discipline.discipline)
else:
destination_disciplines_set.add(
(discipline.institution, discipline.discipline))
destination_disciplines_set = sorted(destination_disciplines_set)
if receiving_is_singleton:
destination_disciplines_str = ''
if len(destination_disciplines_set) > 1:
destination_disciplines_str = '<div>' + \
'</div><div>'.join(destination_disciplines_set) + '</div>'
else:
destination_disciplines_str = ''.join(
destination_disciplines_set)
else:
colleges = {}
for discipline in destination_disciplines_set:
if discipline[0] not in colleges.keys():
colleges[discipline[0]] = []
colleges[discipline[0]].append(discipline[1])
for college in colleges:
destination_disciplines_str += '<div>{}: <em>{}</em></div>'.\
format(institution_names[college], ', '.join(colleges[college]))
# We are showing disciplines, but reporting cuny_subjects.
source_label = ''
source_cbox = ''
source_is_active = ''
if source_disciplines_str != '':
source_is_active = ' active-cell'
source_label = f'<label for="source-subject-{cuny_subject}">{source_disciplines_str}</label>'
source_cbox = f"""
<label for="source-subject-{cuny_subject}">
<input type="checkbox"
id="source-subject-{cuny_subject}"
name="source_subject"
value="{cuny_subject}"/> </label>"""
destination_label = ''
destination_cbox = ''
dest_is_active = ''
if destination_disciplines_str != '':
dest_is_active = ' active-cell'
destination_label = f"""
<label for="destination-subject-{cuny_subject}">{destination_disciplines_str}</label>"""
destination_cbox = f"""
<label for="destination-subject-{cuny_subject}">
<input type="checkbox"
checked="checked"
id="destination-subject-{cuny_subject}"
name="destination_subject"
value="{cuny_subject}"/></label>
"""
selection_rows += f"""
<tr>
<td class="source-subject{source_is_active}">{source_label}</td>
<td class="source-subject f2-cbox{source_is_active}">{source_cbox}</td>
<td><span title="{cuny_subject}">{subject_names[cuny_subject]}</span></td>
<td class="destination-subject f2-cbox{dest_is_active}">{destination_cbox}</td>
<td class="destination-subject{dest_is_active}">{destination_label}</td>
</tr>
"""
num_rows += 1
shortcuts = """
<h2 class="error">
There are no disciplines that match the combination of colleges you selected.
</h2>
"""
if num_rows > 1:
shortcuts = """
<table id="f2-shortcuts">
<tr>
<td f2-cbox" colspan="2">
<div>
<label for="all-source-subjects"><em>Select All Sending Disciplines: </em></label>
<input type="checkbox"
id="all-source-subjects"
name="all-source-subjects" />
</div>
<div>
<label for="no-source-subjects"><em>Clear All Sending Disciplines: </em></label>
<input type="checkbox" id="no-source-subjects" checked="checked"/>
</div>
</td>
<td f2-cbox" colspan="2">
<div>
<label for="all-destination-subjects"><em>Select All Receiving Disciplines: </em>
</label>
<input type="checkbox"
id="all-destination-subjects"
name="all-destination-subjects"
checked="checked"/>
</div>
<div>
<label for="no-destination-subjects"><em>Clear All Receiving Disciplines: </em></label>
<input type="checkbox" id="no-destination-subjects" />
</div>
</td>
</tr>
</table>
"""
# Return Form 2
result = f"""
{header(title='Review Rules: Select Disciplines',
nav_items=[{'type': 'link',
'href': '/',
'text': 'Main Menu'},
{'type': 'link',
'href': '/review_rules',
'text': 'Change Colleges'}])}
<details open>
<summary>
Instructions (click to open/close)
</summary>
<hr>
<p>
There are {len(source_disciplines) + len(destination_disciplines):,} disciplines where
{criterion}.
</p>
<p>
Disciplines are grouped by CUNY subject area. Hover over abbreviations in the first and last
columns for full names.
</p>
<p>
Select at least one sending discipline and at least one receiving discipline.
</p>
<p>
By default, all receiving disciplines are selected to account for all possible equivalencies,
including electives and blanket credit.
</p>
<p>
The next step will show all transfer rules for courses in the corresponding pairs of
disciplines.
</p>
</details>
<form method="post" action="#" id="form-2">
<button id="submit-form-2" type="submit">Next <em>(View Rules)</em></button>
<input type="hidden" name="next-function" value="do_form_2" />
{shortcuts}
<div id="subject-table-div" class="selection-table-div">
<div>
<table id="subject-table" class="scrollable">
<thead>
<tr>
<th class="source-subject">{sending_heading} Discipline(s)</th>
<th class="source-subject">Select Sending</th>
<th>CUNY Subject</th>
<th class="destination-subject">Select Receiving</th>
<th class="destination-subject">{receiving_heading} Discipline(s)</th>
</tr>
</thead>
<tbody>
{selection_rows}
</tbody>
</table>
</div>
</div>
</form>
<div id='form-2-submitted'>
Searching <span class='dot-1'>.</span> <span class='dot-2'>.</span> <span class='dot-3'>.</span>
</div>
"""
response = render_template('review_rules.html', result=Markup(result))
return response
def process_pending(row):
""" Look up the token and generate events. Return as status message.
"""
token = row.token
reviews = json.loads(row.reviews)
email = row.email
when_entered = row.when_entered
summaries = ''
conn = PgConnection()
cursor = conn.cursor()
institutions = set()
for review in reviews:
key = RuleKey._make(review['rule_key'].split(':'))
institutions.add(key.source_institution)
institutions.add(key.destination_institution)
cursor.execute(
"""
select id, review_status
from transfer_rules
where source_institution = %s
and destination_institution = %s
and subject_area = %s
and group_number = %s
""", key)
rule_id, old_status = cursor.fetchone()
# Generate an event for this review
q = """
insert into events (rule_id, event_type,
who, what, event_time)
values (%s, %s, %s, %s, %s)"""
cursor.execute(q, (rule_id, review['event_type'], email,
review['comment_text'], when_entered))
# Update the review state for this rule.
new_status = old_status | abbr_to_bitmask[review['event_type']]
q = 'update transfer_rules set review_status = %s where id = %s'
cursor.execute(q, (new_status, rule_id))
# Generate a summary of this review
old_status_str = status_string(old_status)
new_status_str = status_string(new_status)
# Convert to event-history link for the rule
new_status_str = f"""
<a href="/history/{review['rule_key']}"
target="_blank"
rel="noopener noreferrer">{new_status_str}</a>"""
summaries += f"""
<tr>
{review['rule_str']}
</tr>
"""
# Remove record from pending_reviews
cursor.execute('delete from pending_reviews where token = %s', (token, ))
conn.commit()
conn.close()
suffix = 's'
have_has = 'were'
num_reviews = len(reviews)
if num_reviews == 1:
suffix = ''
have_has = 'was'
if num_reviews < 13:
num_reviews = [
'', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight',
'nine', 'ten', 'eleven', 'twelve'
][num_reviews - 1]
# Return summary as an html table, and the set of institutions affected.
return f"""
<p class="instructions">
The following {num_reviews} transfer rule review{suffix} {have_has} submitted by
<em>{email}</em> on {when_entered.strftime('%B %d, %Y at %I:%M %p')}.
</p>
<table>
<tr>
<th colspan="5">Rule</th>
<th>Previous Review Status<br/><em>(links show review history)</em></th>
<th colspan="2">New Review Status</th>
</tr>
{summaries}
</table>
""", institutions
def shortcut_to_rules(request, session):
""" Given a user's email address, see if they are in the person_roles table, and if so generate
a quick list of rule keys based on some simple additional info.
"""
for key in session.keys():
print(f'{key}: {session[key]}')
remember_me = session['remember_me']
# session.clear()
conn = PgConnection()
cursor = conn.cursor()
# Determine user
email = None
email_prompt = 'Your CUNY email address'
old_value = ''
while email is None:
if 'email' in request.form:
# Request Form overrides session
email = request.form.get('email')
if email.lower().endswith('@cuny.edu') or email.lower().endswith('.cuny.edu'):
session['email'] = request.form.get('email')
session['remember_me'] = request.form.get('remember_me') == 'on'
remember_me = session['remember_me']
else:
old_value = email
email = None
email_prompt = 'CUNY email addresses end with “cuny.edu”'
elif 'email' in session:
email = session['email']
else:
old_value = ''
email = None
email_prompt = 'Your CUNY email address'
if email:
cursor.execute(f"""
select name, email from people where email ~* %s or alternate_emails ~* %s
""", (email, email))
if cursor.rowcount == 1:
row = cursor.fetchone()
name, email = row.name, row.email
# Get roles
cursor.execute(f"""
select role, institution, organization from person_roles where email ~ %s
""", (email, ))
if cursor.rowcount == 0:
old_value = email
email = None
else:
roles = [Role(row.role, row.institution, row.organization) for row in cursor.fetchall()]
print(f'{roles=}')
else:
# Unrecognized (rowcount = 0) or Ambiguous (rowcount > 1) Email
email = None
email_prompt = (f'Unrecognized email address. Change it, or '
'<a href="/review_rules/">Use This Link</a>')
if email is None:
# Get user’s (fixed) email and come back here with it.
email_form = f"""
<h1>Your CUNY email address, please.</h1>
<fieldset><legend>{email_prompt}</legend>
<form method='POST', action='/quick_rules/'>
<label for="email">Email</label> <input type="text"
id="email"
name="email"
value="{old_value}"
size="30"/>
<button type="submit">Submit</button><br>
<label for="remember-me">Remember Me</label> <input type="checkbox"
name="remember_me"
value="{remember_me}"
id="remember-me"/>
</form>
</fieldset
"""
conn.close()
return email_form
debug = f'<h2>{name}</h2>'
# Got email, name, and non-empty list of roles. Which rules to review?
""" If provost and evaluator ask which role (not yet)
If user role includes evaluator
Community college: all active sending courses from organization's subjects, ordered by
frequency of transfer and BKCR-ness at receiving end. Note missing rules.
Senior college: all receiving courses from organization's subjects, ordered by frequency of
transfer.
If user role includes provost (not yet)
Else send to longcut interface
"""
for role in roles:
if role.role == 'webmaster':
pass
elif role.role == 'evaluator':
debug += '<p>Hello Evaluator!</p>'
cursor.execute(f"""
select associates, bachelors from cuny_institutions
where code = '{role.institution}'
""")
assert cursor.rowcount == 1
row = cursor.fetchone()
is_receiving = row.bachelors
is_sending = row.associates
cursor.execute(f"""
select * from cuny_disciplines
where institution = '{role.institution}'
and department = '{role.organization}'
and status = 'A'
""")
assert cursor.rowcount > 0
disciplines = [row.discipline for row in cursor.fetchall()]
debug += f'<p>{is_sending=} {is_receiving=} {disciplines=}</p>'
# Get all course_ids for all disciplines so we can report missing rules
discipline_list = ', '.join(f"'{d}'" for d in disciplines)
cursor.execute(f"""
select course_id, offer_nbr, discipline, catalog_number, title,
designation in ('MLA', 'MNL') as is_mesg,
attributes ~ 'BKCR' as is_bkcr
from cuny_courses
where institution = '{role.institution}'
and discipline in ({discipline_list})
and course_status = 'A'
order by discipline, numeric_part(catalog_number)
""")
assert cursor.rowcount > 0
courses = {r.course_id: Course(r.course_id, r.offer_nbr, r.discipline, r.catalog_number,
r.title, r.is_bkcr, r.is_mesg)
for r in cursor.fetchall() if is_sending or is_receiving and r.is_bkcr}
debug += f'<p>{courses=}</p>'
# Get counts of how many times each course_id transferred
trans_conn = PgConnection('cuny_transfers')
trans_cursor = trans_conn.cursor()
course_id_list = ','.join(f'{courses[k].course_id}' for k in courses.keys())
if is_receiving:
trans_cursor.execute(f"""
select count(*), dst_course_id as course_id
from transfers_applied
where dst_course_id in ({course_id_list})
group by dst_course_id
order by count desc
""")
cursor.execute(f"""
select rule_key(rule_id), array_agg(course_id) as course_ids
from transfer_rules r, destination_courses
where course_id in ({course_id_list})
group by rule_id
""")
if is_sending:
trans_cursor.execute(f"""
select count(*), src_course_id as course_id
from transfers_applied
where src_course_id in ({course_id_list})
group by src_course_id
order by count desc
""")
cursor.execute(f"""
select rule_key(rule_id), array_agg(course_id) as course_ids
from source_courses
where course_id in ({course_id_list})
group by rule_id
""")
xfer_course_counts = defaultdict(int)
for r in trans_cursor.fetchall():
xfer_course_counts[int(r.course_id)] = int(r.count)
rule_keys = defaultdict(int)
for rule in cursor.fetchall():
course_ids = set([int(id) for id in rule.course_ids])
for course_id in course_ids:
rule_keys[rule.rule_key] = course_id
debug += f'<p>{xfer_course_counts=}/</p>'
debug += f'<p>{rule_keys=}</p>'
else:
debug += f'<p>{role.role.title()} Role not implmented yet</p>'
eval_table = """
<table id="eval-table">
<tr>
<th>Sending<br>College</th><th>Sending<br>Courses</th><th>Credits<br>Transferred</th>
<th>Receiving<br>College</th><th>Receiving<br>Courses</th><th>Review<br>Status</th>
</tr>
"""
for rule_key in rule_keys.keys():
row, text = format_rule_by_key(rule_key)
eval_table += row
eval_table += '</table>'
result = f"""
{header(title='Quick Access To Rules',
nav_items=[{'type': 'link',
'href': '/',
'text': 'Main Menu'},
{'type': 'link',
'href': '/review_rules',
'text': 'Change Colleges'},
{'type': 'button',
'class': 'back-button',
'text': 'Change Subjects'
}])}
<details open>
<summary>Instructions (click to open/close)</summary>
<hr>
<p>
Here are {len(rule_keys):,} rules where a course transfers as Blanket Credit.
</p>
</details>
{eval_table}
"""
cursor.close()
trans_cursor.close()
return result
# Generate list of rule_keys
if len(selected_rules) == 0:
num_rules = 'No matching transfer rules found.'
if len(selected_rules) == 1:
num_rules = 'There is one matching transfer rule.'
if len(selected_rules) > 1:
num_rules = f'There are {len(selected_rules):,} matching transfer rules.'
rules_table = format_rules(selected_rules, scrollable=True)
result = f"""
{header(title='Review Rules: Review Selected Rules',
nav_items=[{'type': 'link',
'href': '/',
'text': 'Main Menu'},
{'type': 'link',
'href': '/review_rules',
'text': 'Change Colleges'},
{'type': 'button',
'class': 'back-button',
'text': 'Change Subjects'
}])}
<details open>
<summary>Instructions (click to open/close)</summary>
<hr>
{num_rules}
<p>
Blanket Credit courses are <span class="blanket">highlighted like this</span>.
</p>
<p>
Rules that are <span class="credit-mismatch">highlighted like this</span> have a different
number of credits taken from the number of credits transferred.
Hover over the “=>” to see the numbers of credits.
</p>
<p>
Credits in parentheses give the number of credits transferred where that does not match the
nominal number of credits for a course.
</p>
<p>
Rules that are <span class="evaluated">highlighted like this</span> are ones that you have
reviewed but not yet submitted.
</p>
<p class="call-to-action">
Click on a rule to review it
</p>
</details>
<fieldset id="verification-fieldset"><legend>Review Reviews</legend>
<p id="num-pending">You have not reviewed any transfer rules yet.</p>
<button type="text" id="send-email" disabled="disabled">
Review Your Reviews
</button>
<form method="post" action="#" id="review-form">
Waiting for rules to finish loading ...
</form>
</fieldset>
<div id="rules-table-div" class="selection-table-div table-height">
{rules_table}
</div>
"""
return render_template('review_rules.html', result=Markup(result))
def course_mappings_impl(request):
""" Return a view of the program requirements a course satisfies.
If the instutition, discipline, or course_dict are not known, return a form to get them
instead.
"""
institution = request.args.get('college')
discipline = request.args.get('discipline')
catalog_number = request.args.get('catalog-number')
course_dict = None
show_majors = request.args.get('show-majors')
show_minors = request.args.get('show-minors')
show_concs = request.args.get('show-concs')
# if nothing selected, select all
if show_majors is None and show_minors is None and show_concs is None:
show_majors = 'majors'
show_minors = 'minors'
show_concs = 'concs'
program_types = []
if show_majors == 'majors':
program_types.append('MAJOR')
show_majors = ' checked="checked'
if show_minors == 'minors':
program_types.append('MINOR')
show_minors = ' checked="checked'
if show_concs == 'concs':
program_types.append('CONC')
show_concs = ' checked="checked'
if len(program_types) == 0:
program_types = ['MAJOR', 'MINOR', 'CONC']
try:
range_index = int(request.args.get('count-limit'))
count_limit = [1, 2, 5, 10, 20, 50, 100, 999999][range_index]
except TypeError as te:
range_index = 7
count_limit = 999999
if count_limit > 100:
range_string = 'all'
else:
range_string = f'{count_limit}'
header_str = header(title='Requirements by Course',
nav_items=[{
'type': 'link',
'text': 'Main Menu',
'href': '/'
}, {
'type': 'link',
'text': 'Programs',
'href': '/registered_programs'
}])
result = f'{header_str}'
result += """
<h1>Explore the Course-to-Requirements Database</h1>
<div class="instructions">
<p>
Select a course, to see what program requirements it satisfies. Programs include all Majors,
Minors, and Concentrations currently offered at the selected college.
</p>
<p>
Requirements are extracted from Degree Works Scribe Blocks.
</p>
<p class="error">This project is still under development. Please report errors and feature
requests to <a href="mailto:[email protected]?subject='Program Requirements'">Christopher
Vickery</a>
</p>
</div>
"""
conn = PgConnection()
cursor = conn.cursor()
# Look up all colleges; preselect one if known.
cursor.execute('select code, prompt from cuny_institutions')
if institution:
college_choices = f'<details><summary>Select College ({institution})</summary><hr>'
else:
college_choices = f'<details open="open"><summary>Select College</summary><hr>'
if len(colleges_indexed):
college_choices += """
<p>
The numbers tell how many (majors / minors / concentrations) have been indexed for each college.
During development, not all colleges are being indexed. If a college is not listed here it means
it is not currently indexed.
</p>
"""
else:
college_choices += 'No colleges are currently indexed.'
college_choices += '<div class="selections">'
for college, counts in colleges_indexed.items():
num_majors = num_minors = num_concs = 0
if 'MAJOR' in counts.keys():
num_majors = counts['MAJOR']
if 'MINOR' in counts.keys():
num_minors = counts['MINOR']
if 'CONC' in counts.keys():
num_concs = counts['CONC']
if num_majors + num_minors + num_concs > 0:
if college == institution:
selected_item = ' class="selected-item"'
checked_attr = 'checked="checked"'
else:
selected_item = checked_attr = ''
college_choices += f"""
<div class="radio-container">
<input id="radio-{college}" {checked_attr}
type="radio"
name="college"
value="{college}" />
<label{selected_item} for="radio-{college}">{college_names[college]}<br/>
<span>({num_majors} / {num_minors} / {num_concs})</span></label>
</div>
"""
college_choices += '</div></details>'
# If there is an institution, display all disciplines
if institution:
submit_prompt = 'Select a discipline or a different college'
if discipline:
discipline_choices = f'<details><summary>Select Discipline ({discipline})</summary><hr>'
else:
discipline_choices = f'<details open="open"><summary>Select Discipline</summary><hr>'
discipline_choices += '<div class="selections">'
cursor.execute(
"""
select discipline, discipline_name, department
from cuny_disciplines
where institution = %s
and cuny_subject != 'MESG'
and status = 'A'
""", (institution, ))
for row in cursor.fetchall():
if discipline and discipline.lower() == row.discipline.lower():
selected_item = ' class="selected-item"'
checked_attr = 'checked="checked"'
else:
selected_item = checked_attr = ''
discipline_choices += f"""
<div class="radio-container">
<input id="radio-{row.discipline}" {checked_attr}
type="radio"
name="discipline"
value="{row.discipline}" />
<label{selected_item} for="radio-{row.discipline}">
{row.discipline} ({row.discipline_name})
</label>
</div>
"""
discipline_choices += '</div></details>'
else:
submit_prompt = 'Select a College'
discipline_choices = ''
# If there is a discipline, display all courses
if discipline:
if catalog_number:
submit_prompt = 'Change course, discipline, and/or college'
catalog_choices = (
f'<details><summary>Select Course ({discipline} {catalog_number})'
f'</summary><hr>')
else:
submit_prompt = 'Select a course, or a different discipline and/or college'
catalog_choices = f'<details open="open"><summary>Select Course</summary><hr>'
cursor.execute(
"""
select course_id, offer_nbr, discipline, catalog_number, title
from cuny_courses
where institution = %s
and discipline = %s
and career = 'UGRD'
and designation not in ('MLA', 'MNL')
and course_status = 'A'
order by numeric_part(catalog_number)
""", (institution, discipline))
if cursor.rowcount == 0:
catalog_choices += f'<p class="error">No Courses Found</p>'
for row in cursor.fetchall():
if catalog_number and row.catalog_number == catalog_number:
selected_item = ' class="selected-item"'
checked_attr = ' checked="checked"'
course_dict = {
'course_id': row.course_id,
'catalog_number': row.catalog_number,
'title': row.title
}
else:
selected_item = checked_attr = ''
catalog_choices += f"""
<div class="radio-container">
<input type="radio" {checked_attr}
name="catalog-number"
id="catalog-number-{row.course_id}"{checked_attr}
value="{row.catalog_number}"/>
<label{selected_item} for="catalog-number-{row.course_id}">
{row.discipline} {row.catalog_number}: <em>{row.title}</em>
</label>
</div>
"""
catalog_choices += '</details>'
else:
submit_prompt = 'Select a discipline or a different college'
catalog_choices = ''
conn.close()
if course_dict:
course_mapping_html = _to_html(institution, discipline, course_dict,
program_types, count_limit)
else:
course_mapping_html = '<p class="warning">No Course Selected Yet</p>'
result += f"""
<form method="GET" action="/course_mappings">
<div class="instructions">
<p>
There can be a lot of “clutter” in what gets displayed here. You can uncheck some of the
checkboxes below to exclude the types of requirements you are not interested in.
</p>
<div>
<div class="inline">
<input type="checkbox" id="show-majors" name="show-majors" value="majors"{show_majors}/>
<label for="show-majors">Majors</label>
</div>
<div class="inline">
<input type="checkbox" id="show-minors" name="show-minors" value="minors"{show_minors}/>
<label for="show-minors">Minors</label>
</div>
<div class="inline">
<input type="checkbox" id="show-concs" name="show-concs" value="concs"{show_concs}/>
<label for="show-concs">Concentrations</label>
</div>
</div>
<hr>
<p>
Another potential source of clutter is requirements that can be satisified by a large number
of courses. You an use the slider below to filter out requirements based on how many courses
can satisfy them.
</p>
<input id="slider"
name="count-limit" type="range" min="0" max="7" step="1.0" value="{range_index}"/>
<span>{range_string}</span>
</div>
<div id="select-institution">
{college_choices}
</div>
<div id="select-discipline">
{discipline_choices}
</div>
<div id="select-catalog-num">
{catalog_choices}
</div>
<div>
<button type="submit" id="goforit">{submit_prompt}</button>
</div>
<div id="course-mapping">
{course_mapping_html}
</div>
</form>
"""
return result
def do_form_3(request, session):
if DEBUG:
print(f'*** do_form_3({session})')
reviews = json.loads(request.form['reviews'])
kept_reviews = [e for e in reviews if e['include']]
email = session['email']
if len(kept_reviews) == 0:
result = '<h1>There are no reviews to confirm.</h1>'
else:
message_tail = 'review'
if len(kept_reviews) > 1:
num_reviews = len(kept_reviews)
if num_reviews < 13:
num_reviews = [
'', 'two', 'three', 'four', 'five', 'six', 'seven',
'eight', 'nine', 'ten', 'eleven', 'twelve'
][num_reviews - 1]
message_tail = '{} reviews'.format(num_reviews)
# Insert these reviews into the pending_reviews table of the db.
conn = PgConnection()
cursor = conn.cursor()
token = str(uuid.uuid4())
reviews = json.dumps(kept_reviews)
q = "insert into pending_reviews (token, email, reviews) values(%s, %s, %s)"
cursor.execute(q, (token, email, reviews))
conn.commit()
conn.close()
# Description message templates
review_dict = dict()
review_dict['ok'] = '{}: OK'
review_dict['not-ok'] = '{}: {}'
review_dict['other'] = 'Other: {}'
# Generate description messages
style_str = ' style="border:1px solid #666;vertical-align:middle; padding:0.5em;"'
suffix = 's'
if len(kept_reviews) == 1:
suffix = ''
review_rows = f"""
<table style="border-collapse:collapse;">
<tr>
<th colspan="5"{style_str}>Rule</th>
<th{style_str}>Current Status<br><em>(links show review history)</em></th>
<th colspan="2"{style_str}>Your Review{suffix}</th>
</tr>
"""
for review in kept_reviews:
review_rows += review['rule_str']
review_rows += '</table>'
# Send the email
url = request.url_root + 'confirmation/' + token
response = send_token(email, url, review_rows)
if response.status_code != 202:
result = f'Error sending email: {response.body}'
else:
result = f"""
{header(title='Review Rules: Respond to Email',
nav_items = [
{'type': 'link',
'href': '/',
'text': 'Main Menu'},
{'type': 'link',
'href': '/review_rules',
'text':'Review More Rules'}])}
<details>
<summary>Check your email at {email}</summary>
<hr>
<p>
Click on the 'activate these reviews' button in that email
to confirm that you actually wish to have your {message_tail} recorded.
</p>
</details>
<h2>
Thank you for your work!
</h2>
"""
return render_template('review_rules.html', result=Markup(result))
from typing import Dict
from pgconnection import PgConnection
# Copy of the review_status_bits table
# value: bitmask
# abbr: short text
# description: long text
# Dicts for looking up status bit information.
bitmask_to_description: Dict[int, str] = dict()
abbr_to_bitmask: Dict[str, int] = dict()
event_type_bits: Dict[int, str] = dict()
conn = PgConnection()
with conn.cursor() as cursor:
cursor.execute('select * from review_status_bits')
for row in cursor.fetchall():
abbr_to_bitmask[row.abbr] = row.bitmask
bitmask_to_description[row.bitmask] = row.description
conn.close()
def get_abbr_to_bitmask():
return abbr_to_bitmask
def status_string(status):
"""
Generate a string summarizing all bits that are set in status.
"""
def build_course_list(ctx, institution, requirement_id) -> dict:
"""
course_list : course_item (and_list | or_list)? (except_list | include_list)*
proxy_advice? label?;
full_course : discipline catalog_number with_clause*; // Used only in expressions
course_item : area_start? discipline? catalog_number with_clause* area_end?;
and_list : (list_and area_end? course_item)+ ;
or_list : (list_or area_end? course_item)+ ;
except_list : EXCEPT course_item (and_list | or_list)?; // Always OR
include_list : INCLUDING course_item (and_list | or_list)?; // Always AND
The returned dict has the following structure:
Scribed and Active course lists.
scribed_courses List of all (discipline, catalog_number, with_clause) tuples in the list
after distributing disciplines across catalog_numbers. (Show "BIOL 1, 2"
as "BIOL 1, BIOL 2")
active_courses Catalog information and WITH clause (if any) for all active courses that
match the scribed_courses list after expanding wildcards and
catalog_number ranges.
inactive_courses Catalog information for all inactive courses that match the scribed
course list after wildcard and range expansions.
missing_courses Explicitly-scribed courses that do not exist in CUNYfirst.
course_areas List of active_courses divided into distribution areas; presumably the
full course list will have a MinArea qualifier, but this is not checked
here. Omit inactive, missing, and except courses because they are
handled in the full course list.
except_courses Scribed list used for culling from active_courses.
include_courses Like except_courses, except this list is not actually used for anything
in this method.
list_type 'AND' or 'OR'
attributes List of all attribute values the active courses list have in common,
currently limited to WRIC and BKCR
Missing courses: Any explicitly-scribed course that fails course catalog lookup. Obviously,
wildcard-expanded lists will find only active and inactive courses, and thus will never add
to the missing courses list
The except_courses list is an OR list no matter how it is scribed. (Ellucian accepts either
conjunction, even though documentation says AND is illegal.)
The include_courses list is an AND list no matter how it is scribed. (Ellucian documentation
makes this explicit.)
"""
if DEBUG:
print(f'*** build_course_list({institution}, {class_name(ctx)})',
file=sys.stderr)
if ctx is None:
return None
assert class_name(
ctx) == 'Course_list', f'{class_name(ctx)} is not Course_list'
# The dict to be returned:
return_dict = {
'tag': 'course_list',
'scribed_courses': [],
'list_type': '',
'qualifiers': [],
'label': None,
'active_courses': [],
'inactive_courses': [],
'except_courses': [],
'include_courses': [],
'course_areas': [],
'missing_courses': [],
'attributes': []
}
# Shortcuts to the lists in return_dict
scribed_courses = return_dict['scribed_courses']
qualifiers = return_dict['qualifiers']
active_courses = return_dict['active_courses']
inactive_courses = return_dict['inactive_courses']
except_courses = return_dict['except_courses']
include_courses = return_dict['include_courses']
missing_courses = return_dict['missing_courses']
attributes = return_dict['attributes']
# The Scribe context in which the list appeared
return_dict['context_path'] = context_path(ctx)
# Pick up the label, if there is one
if ctx.label():
return_dict['label'] = ctx.label().string().getText().strip(
'"').replace('\'', '’')
# get context of the required course_item and list of optional additional course_items.
course_item = ctx.course_item()
if ctx.and_list():
return_dict['list_type'] = 'AND'
list_items = ctx.and_list().course_item()
elif ctx.or_list():
return_dict['list_type'] = 'OR'
list_items = ctx.or_list().course_item()
else:
return_dict['list_type'] = 'None'
list_items = []
scribed_courses += get_scribed_courses(course_item, list_items)
# Explanation of lists: the grammar says
if ctx.except_list():
course_item = ctx.except_list()[0].course_item()
# Ellucian allows either AND or OR even though it has to be OR
if ctx.except_list()[0].and_list():
list_items = ctx.except_list()[0].and_list().course_item()
elif ctx.except_list()[0].or_list():
list_items = ctx.except_list()[0].or_list().course_item()
else:
list_items = []
except_courses += get_scribed_courses(course_item, list_items)
if ctx.include_list():
course_item = ctx.include_list()[0].course_item()
# Ellucian allows either AND or OR even though it has to be OR
if ctx.include_list()[0].and_list():
list_items = ctx.include_list()[0].and_list().course_item()
elif ctx.include_list()[0].or_list():
list_items = ctx.include_list()[0].or_list().course_item()
else:
list_items = []
include_courses += get_scribed_courses(course_item, list_items)
qualifiers = get_qualifiers(ctx, institution, requirement_id)
# Active Courses (skip if no institution given, such as in a course list qualifier course list)
all_blanket = True
all_writing = True
check_missing = True # Unless there are wildcards or ranges
conn = PgConnection()
cursor = conn.cursor()
current_area = None
for scribed_course in scribed_courses:
# Start and end course areas. Active courses will be added to current_area if it exists
if scribed_course == 'area_start':
current_area = []
continue
if scribed_course == 'area_end':
if current_area and len(current_area) > 0:
return_dict['course_areas'].append(current_area)
current_area = None
continue
# For display to users
display_discipline, display_catalog_number, display_with_clause = scribed_course
# For course db query
discipline, catalog_number, with_clause = scribed_course
# discipline part
discp_op = '='
if '@' in discipline:
discp_op = '~*'
check_missing = False
discipline = '^' + discipline.replace('@', '.*') + '$'
# 0@ means any catalog number < 100 according to the Scribe manual, but CUNY has no catalog
# numbers that start with zero. But other patterns might be used: 1@, for example.
catalog_numbers = catalog_number.split(':')
if len(catalog_numbers) == 1:
if '@' in catalog_numbers[0]:
check_missing = False
catnum_clause = "catalog_number ~* '^" + catalog_numbers[
0].replace('@', '.*') + "$'"
else:
catnum_clause = f"catalog_number = '{catalog_numbers[0]}'"
else:
check_missing = False
low, high = catalog_numbers
# Assume no wildcards in range ...
try:
catnum_clause = f"""(numeric_part(catalog_number) >= {float(low)} and
numeric_part(catalog_number) <=' {float(high)}')
"""
except ValueError:
# ... but it looks like there were.
check_missing = False
# Assume:
# - the range is being used for a range of course levels (1@:3@, for example)
# - catalog numbers are 3 digits (so 1@ means 100 to 199, for example
# Otherwise, 1@ would match 1, 10-19, 100-199, and 1000-1999, which would be strange, or
# at least fragile in the case of Lehman, which uses 2000-level numbers for blanket
# credit courses at the 200 level.
matches = re.match('(.*?)@(.*)', low)
if matches.group(1).isdigit():
low = matches.group(1) + '00'
matches = re.match('(.*?)@(.*)', high)
if matches.group(1).isdigit():
high = matches.group(1) + '99'
catnum_clause = f"""(numeric_part(catalog_number) >= {float(low)} and
numeric_part(catalog_number) <= {float(high)})
"""
else:
# Either low or high is not in the form: \d+@
catnum_clause = "catalog_number = ''" # Will match no courses
course_query = f"""
select institution, course_id, offer_nbr, discipline, catalog_number, title,
requisites, description, course_status, contact_hours, max_credits, designation,
replace(regexp_replace(attributes, '[A-Z]+:', '', 'g'), ';', ',')
as attributes
from cuny_courses
where institution ~* '{institution}'
and discipline {discp_op} '{discipline}'
and {catnum_clause}
order by discipline, numeric_part(catalog_number)
"""
if DEBUG:
print(f'{discp_op=} {discipline=} {catnum_clause=}',
file=sys.stderr)
cursor.execute(course_query)
if cursor.rowcount > 0:
for row in cursor.fetchall():
# skip excluded courses
if (row.discipline, row.catalog_number, ANY) in except_courses:
continue
if row.course_status == 'A':
active_course_tuple = (row.course_id, row.offer_nbr,
row.discipline, row.catalog_number,
row.title, with_clause)
active_courses.append(active_course_tuple)
if current_area is not None:
current_area.append(active_course_tuple)
# Check BKCR and WRIC only for active courses
if row.max_credits > 0 and 'BKCR' not in row.attributes:
# if all_blanket:
# print(f'*** wet blanket: {row.course_id} {row.discipline} {row.catalog_number} '
# f'{row.max_credits} {row.attributes}', file=sys.stderr)
all_blanket = False
if 'WRIC' not in row.attributes:
all_writing = False
else:
inactive_courses.append(
(row.course_id, row.offer_nbr, row.discipline,
row.catalog_number, row.title, with_clause))
conn.close()
if len(active_courses) > 0:
if all_blanket:
attributes.append('Blanket Credit')
if all_writing:
attributes.append('Writing Intensive')
# Clean out any (area_start and area_end) strings from the scribed_courses list
return_dict['scribed_courses'] = [
item for item in return_dict['scribed_courses']
if isinstance(item, tuple)
]
# Make sure each scribed course was found. Check only if there were no wildcards scribed.
if check_missing:
found_courses = [(course[2], course[3]) for course in active_courses]
found_courses += [(course[2], course[3])
for course in inactive_courses]
for scribed_course in return_dict['scribed_courses']:
if (scribed_course[0], scribed_course[1]) not in found_courses:
missing_courses.append(scribed_course)
return return_dict
def format_course(course, active_only=False):
""" Given a named tuple returned by institution_query or course_query, generate an html "catalog"
entry for the course
"""
# if one of the components is the primary component (it should be), make it the first one.
components = course.components
if len(course.components) > 1:
components = dict(course.components)
primary_component = [
course.primary_component,
components.pop(course.primary_component, None)
]
components = [[component, components[component]]
for component in components.keys()]
components.insert(0, primary_component)
component_str = ', '.join([
f'{component[1]} hr {component[0].lower()}' for component in components
])
if math.isclose(course.min_credits, course.max_credits):
credits_str = f'{component_str}; {course.min_credits:0.1f} cr.'
else:
credits_str = f'{component_str}; {course.min_credits:0.1f}–{course.max_credits:0.1f} cr'
transfer_link = (
f'https://explorer.cuny.edu/course-transfer/{course.course_id:06}/'
f'{course.offer_nbr}')
title_str = f"""<a href="{transfer_link}" style="text-decoration: none;">
<strong>{course.discipline} {course.catalog_number}: {course.title}</strong></a>
<br/>Requisites: {course.requisites}"""
properties_str = f"""(<em>{course.career}; {course.cuny_subject}; {course.designation};
{', '.join(course.attributes.split(';'))}</em>)"""
if course.course_id in cross_listed:
# For cross-listed courses, it’s normal for the cuny_subject and requisites to change across
# members of the group.
# But it would be an error for career, requisites, description, designation, etc. to vary, so
# we assume they don’t. (There are two known cases of career errors, which OUR is correcting
# as we speak. There are no observed errors of the other types.)
# There is no way to get a different attributes list, because those depend only on course_id.
title_str += '<br/>Cross-listed with:'
conn = PgConnection()
cursor = conn.cursor()
cursor.execute(
"""
select c.discipline, c.catalog_number, c.title,
cc. description as career, s.subject_name as cuny_subject,
c.designation, c.requisites, c.attributes
from cuny_courses c, cuny_subjects s, cuny_careers cc
where course_id = %s
and offer_nbr != %s
and cc.career = c.career
and cc.institution = c.institution
and s.subject = c.cuny_subject
order by discipline, catalog_number
""", (course.course_id, course.offer_nbr))
for cross_list in cursor.fetchall():
title_str += f"""<br/>
<strong>{cross_list.discipline} {cross_list.catalog_number}: {cross_list.title}</strong>
(<em>{cross_list.career}, {cross_list.cuny_subject}, {cross_list.attributes}</em>)
<br/>Requisites: {cross_list.requisites}
"""
cursor.close()
conn.close()
note = ''
if not active_only:
if course.course_status != 'A':
note = '<div class="warning"><strong>Note:</strong> Course is not active in CUNYfirst</div>'
hover_text = f'course id: {course.course_id:06}.{course.offer_nbr}; click for transfer info'
html = f"""
<p class="catalog-entry" title="{hover_text}">{title_str}
<br/>{credits_str}
<br/>{course.description}
<br/>{properties_str}
</p>
{note}
"""
return html
def lookup_courses(institution,
active_only=True,
department=None,
discipline=None):
""" Lookup all the active courses for an institution. Return giant html string.
Can restrict to courses offered by a particular department and/or in a particular discipline.
"""
conn = PgConnection()
cursor = conn.cursor()
# Active courses require both the course and the discipline to be active
if active_only:
which_courses = """
and c.course_status = 'A'
and c.can_schedule = 'Y'
and c.discipline_status = 'A'
"""
else:
# Always suppress courses that cannot be scheduled
which_courses = """
and c.can_schecule = 'Y'
"""
if department is None:
department_clause = ''
else:
department_clause = f"and c.department = '{department}'"
if discipline is None:
discipline_clause = ''
else:
discipline_clause = f"and c.discipline = '{discipline}'"
# Course info for all courses at an institution
institution_query = f"""
select c.course_id as course_id,
c.offer_nbr as offer_nbr,
i.name as institution,
s.subject_name as cuny_subject,
d.department_name as department,
c.discipline as discipline,
trim(both from c.catalog_number) as catalog_number,
c.title as title,
c.primary_component as primary_component,
c.components as components,
c.min_credits as min_credits,
c.max_credits as max_credits,
c.requisites as requisites,
c.description as description,
cc.description as career,
c.designation as rd,
rd.description as designation,
c.course_status as course_status,
c.attributes as attributes
from cuny_courses c,
cuny_institutions i,
cuny_departments d,
cuny_subjects s,
cuny_careers cc,
designations rd
where c.institution = %s
{which_courses}
and i.code = c.institution
and d.institution = c.institution
and d.department = c.department
and s.subject = c.cuny_subject
and cc.institution = c.institution
and cc.career = c.career
and rd.designation = c.designation
{department_clause} {discipline_clause}
order by discipline, numeric_part(catalog_number)
"""
cursor.execute(institution_query, (institution, ))
html = ''
for course in cursor.fetchall():
html += format_course(course)
cursor.close()
conn.close()
return html
def do_form_0(request, session):
"""
No form submitted yet; generate the Form 1 page.
"""
if DEBUG:
print(f'*** do_form_0({session})')
conn = PgConnection()
cursor = conn.cursor()
cursor.execute("select count(*) from transfer_rules")
num_rules = cursor.fetchone()[0]
cursor.execute("select * from updates")
updates = cursor.fetchall()
rules_date = 'unknown'
for update in updates:
if update.table_name == 'transfer_rules':
rules_date = datetime.fromisoformat(update.update_date)\
.strftime('%B %e, %Y')
cursor.close()
conn.close()
source_prompt = """
<fieldset id="sending-field"><h2>Sending College(s)</h2>
<div id="source-college-list">
"""
n = 0
for code in institution_names:
n += 1
source_prompt += """
<div class='institution-select'>
<input type="checkbox" name="source" class="source" id="source-{}" value="{}">
<label for="source-{}">{}</label>
</div>
""".format(n, code, n, institution_names[code])
source_prompt += """
</div>
<div>
<button type="button" id="all-sources">Select All Sending Colleges</button>
<button type="button" id="no-sources">Clear All Sending Colleges</button>
</div>
</fieldset>
"""
destination_prompt = """
<fieldset id="receiving-field"><h2>Receiving College(s)</h2>
<div id="destination-college-list">
"""
n = 0
for code in institution_names:
n += 1
destination_prompt += """
<div class='institution-select'>
<input type="checkbox" name="destination" class="destination" id="dest-{}" value="{}">
<label for="dest-{}">{}</label>
</div>
""".format(n, code, n, institution_names[code])
destination_prompt += """
</div>
<div>
<button type="button" id="all-destinations">Select All Receiving Colleges</button>
<button type="button" id="no-destinations">Clear All Receiving Colleges</button>
</div>
</fieldset>
"""
email = ''
if 'email' in session:
email = session['email']
remember_me = ''
if 'remember-me' in session:
remember_me = 'checked="checked"'
# Return Form 1
result = f"""
{header(title='Review Rules: Select Colleges',
nav_items=[{'type': 'link',
'href': '/',
'text': 'Main Menu'}])}
<DETAILS>
<summary>
Instructions
</summary>
<hr>
<p>
This is the first step for reviewing, and optionally commenting on, the {num_rules:,}
existing course transfer rules at CUNY.
</p>
<p>
To see just the rules you are interested in, start here by selecting exactly one sending
college and at least one receiving college, or exactly one receiving college and one or more
sending colleges.
<br/>
In the next step you will select just the discipline(s) you are interested in, and in the
last step you will be able to review the rules that match your selections from the first two
steps.
</p>
<p>
Background information and <em>(much!)</em> more detailed instructions are available in the
<a target="_blank"
rel="noopener noreferrer"
href="https://docs.google.com/document/d/141O2k3nFCqKOgb35-VvHE_A8OV9yg0_8F7pDIw5o-jE">
Reviewing CUNY Transfer Rules</a> document.
</p>
</details>
<fieldset>
<form method="post" action="#" id="form-1">
{source_prompt}
{destination_prompt}
<fieldset>
<h2>Your email address</h2>
<p>
To record your comments and suggestions concerning existing transfer rules, you need to
supply a valid CUNY email address here for verification purposes.<br/>If you just want
to view the rules, you can use a dummy address, such as <em>[email protected]</em>.
</p>
<label for="email-text">Enter a valid CUNY email address:</label>
<div>
<input type="text" name="email" id="email-text" value="{email}"/>
<div>
<input type="checkbox" name="remember-me" id="remember-me" {remember_me}/>
<label for="remember-me"><em>Remember me on this computer.</em></label>
</div>
</div>
<div id="error-msg" class="error"> </div>
<input type="hidden" name="next-function" value="do_form_1" />
<div>
<button type="submit" id="submit-form-1">Next (<em>select disciplines)</em></button>
</div>
</fieldset>
</form>
</fieldset>
<hr>
<div id="update-info">
<p>CUNYfirst information last updated {rules_date}</p>
</div>
"""
response = render_template('review_rules.html',
title='Select Colleges',
result=Markup(result))
return response
def do_form_2(request, session):
"""
Process CUNY Subject list from form 2.
Generate form_3: the selected transfer rules for review
"""
if DEBUG:
print(f'*** do_form_2({session})')
for k, v in session.items():
print(f'{k}: {v}')
conn = PgConnection()
cursor = conn.cursor()
# Look up transfer rules where the sending course belongs to a sending institution and is one of
# the source disciplines and the receiving course belongs to a receiving institution and is one of
# the receiving disciplines.
try:
source_institution_params = ', '.join(
'%s' for i in session['source_institutions'])
destination_institution_params = ', '.join(
'%s' for i in session['destination_institutions'])
except KeyError:
# the session is expired or invalid. Go back to Step 1.
return render_template('review_rules.html',
result=Markup("""
<h1>Session Expired</h1>
<p>
<a href="/" class="button">
Main Menu</a>
<a href="/review_rules"
class="restart button">Restart
</a>
</p>
"""))
# Be sure there is the possibility there will be some rules
source_subject_list = request.form.getlist('source_subject')
destination_subject_list = request.form.getlist('destination_subject')
if len(source_subject_list) < 1:
return render_template(
'review_rules.html',
result=Markup(
'<h1 class="error">No sending disciplines selected.</h1>'))
if len(destination_subject_list) < 1:
return render_template(
'review_rules.html',
result=Markup(
'<h1 class="error">No receiving disciplines selected.</h1>'))
# Prepare the query to get the set of rules that match the institutions and cuny_subjects
# selected.
if request.form.get('all-source-subjects'):
source_subjects_clause = ''
else:
source_subjects_str = '|'.join(f':{s}:' for s in source_subject_list)
source_subjects_clause = f" and '{source_subjects_str}' ~ source_subjects"
source_subjects = ', '.join(f"'{s}'" for s in source_subject_list)
source_subjects_clause = f"""
and id in (select rule_id from subject_rule_map where subject in ({source_subjects}))"""
# Get all the rules where,
# - The source and destination institutions have been selected
# and
# - The source_subjects have been selected
q = f"""
select *
from transfer_rules
where source_institution in ({source_institution_params})
and destination_institution in ({destination_institution_params})
{source_subjects_clause}
order by source_institution, destination_institution, subject_area, group_number"""
cursor.execute(
q,
(session['source_institutions'] + session['destination_institutions']))
if cursor.rowcount < 1:
return render_template(
'review_rules.html',
result=Markup(
'<h1 class="error">There are no matching rules.</h1>'))
all_rules = cursor.fetchall()
selected_rules = []
# Get the source and destination course lists from the above set of rules where the destination
# subject was selected. It's possible to have selected rules that don’t transfer to any of the
# selected destination subjects, so those rules are dropped while building the selected-rules
# list.
if request.form.get('all-destination-subjects'):
destination_subjects_clause = ''
else:
# Create a clause that makes sure the destination course has one of the destination subjects
destination_subject_list = request.form.getlist('destination_subject')
destination_subject_params = ', '.join(
f"'{s}'" for s in destination_subject_list)
destination_subjects_clause = f" and dc.cuny_subject in ({destination_subject_params})"
for rule in all_rules:
# It’s possible some of the selected rules don’t have destination courses in any of the selected
# disciplines, so that has to be checked first.
cursor.execute(
f"""
select dc.course_id,
dc.offer_nbr,
dc.offer_count,
dc.discipline,
dc.catalog_number,
dn.discipline_name,
dc.cuny_subject,
dc.cat_num,
dc.transfer_credits,
dc.credit_source,
dc.is_mesg,
dc.is_bkcr
from destination_courses dc, cuny_disciplines dn
where dc.rule_id = %s
and dn.institution = %s
and dn.discipline = dc.discipline
{destination_subjects_clause}
order by discipline, cat_num
""", (rule.id, rule.destination_institution))
if cursor.rowcount > 0:
destination_courses = [
Destination_Course._make(c) for c in cursor.fetchall()
]
cursor.execute(
"""
select sc.course_id,
sc.offer_nbr,
sc.offer_count,
sc.discipline,
sc.catalog_number,
dn.discipline_name,
sc.cuny_subject,
sc.cat_num,
sc.min_credits,
sc.max_credits,
sc.min_gpa,
sc.max_gpa
from source_courses sc, cuny_disciplines dn
where sc.rule_id = %s
and dn.institution = %s
and dn.discipline = sc.discipline
order by discipline, cat_num
""", (rule.id, rule.source_institution))
if cursor.rowcount > 0:
source_courses = [
Source_Course._make(c) for c in cursor.fetchall()
]
# Create the Transfer_Rule tuple suitable for passing to format_rules, and add it to the
# list of rules to pass.
selected_rules.append(
Transfer_Rule._make([
rule.id, rule.source_institution,
rule.destination_institution, rule.subject_area,
rule.group_number, rule.source_disciplines,
rule.source_subjects, rule.review_status, source_courses,
destination_courses
]))
cursor.close()
conn.close()
if len(selected_rules) == 0:
num_rules = 'No matching transfer rules found.'
if len(selected_rules) == 1:
num_rules = 'There is one matching transfer rule.'
if len(selected_rules) > 1:
num_rules = f'There are {len(selected_rules):,} matching transfer rules.'
rules_table = format_rules(selected_rules, scrollable=True)
result = f"""
{header(title='Review Rules: Review Selected Rules',
nav_items=[{'type': 'link',
'href': '/',
'text': 'Main Menu'},
{'type': 'link',
'href': '/review_rules',
'text': 'Change Colleges'},
{'type': 'button',
'class': 'back-button',
'text': 'Change Subjects'
}])}
<details open>
<summary>Instructions (click to open/close)</summary>
<hr>
{num_rules}
<p>
Blanket Credit courses are <span class="blanket">highlighted like this</span>.
</p>
<p>
Rules that are <span class="credit-mismatch">highlighted like this</span> have a different
number of credits taken from the number of credits transferred.
Hover over the “=>” to see the numbers of credits.
</p>
<p>
Credits in parentheses give the number of credits transferred where that does not match the
nominal number of credits for a course.
</p>
<p>
Rules that are <span class="evaluated">highlighted like this</span> are ones that you have
reviewed but not yet submitted.
</p>
<p class="call-to-action">
Click on a rule to review it
</p>
</details>
<fieldset id="verification-fieldset"><legend>Review Reviews</legend>
<p id="num-pending">You have not reviewed any transfer rules yet.</p>
<button type="text" id="send-email" disabled="disabled">
Review Your Reviews
</button>
<form method="post" action="#" id="review-form">
Waiting for rules to finish loading ...
</form>
</fieldset>
<div id="rules-table-div" class="selection-table-div table-height">
{rules_table}
</div>
"""
return render_template('review_rules.html', result=Markup(result))
def dgw_interpreter(institution: str,
block_type: str,
block_value: str,
period='all',
update_db=True,
verbose=False) -> tuple:
""" For each matching Scribe Block, parse the block and generate lists of JSON objects from it.
The period argument can be 'all', 'current', or 'latest', with the latter two being picked
out of the result set for 'all'
"""
if DEBUG:
print(
f'*** dgw_interpreter({institution}, {block_type}, {block_value}, {period})'
)
conn = PgConnection()
fetch_cursor = conn.cursor()
update_cursor = conn.cursor()
query = """
select institution, requirement_id, title, period_start, period_stop, requirement_text
from requirement_blocks
where institution = %s
and block_type = %s
and block_value = %s
order by period_stop desc
"""
fetch_cursor.execute(query, (institution, block_type, block_value))
# Sanity Check
if fetch_cursor.rowcount < 1:
print(f'No Requirements Found\n{fetch_cursor.query}', file=sys.stderr)
return (None, None)
num_rows = fetch_cursor.rowcount
num_updates = 0
for row in fetch_cursor.fetchall():
if verbose:
print(
f'{institution} {row.requirement_id} {block_type} {block_value} ',
end='',
file=sys.stderr)
if period == 'current' and row.period_stop != '99999999':
print(f'Not currently offered.', end='', file=sys.stderr)
else:
print(catalog_years(row.period_start, row.period_stop).text,
end='',
file=sys.stderr)
print(file=sys.stderr)
# Filter out everything after END, plus hide-related tokens (but not hidden content).
text_to_parse = dgw_filter(row.requirement_text)
# Generate the parse tree from the Antlr4 parser generator.
input_stream = InputStream(text_to_parse)
lexer = ReqBlockLexer(input_stream)
token_stream = CommonTokenStream(lexer)
parser = ReqBlockParser(token_stream)
parse_tree = parser.req_block()
# Walk the head and body parts of the parse tree, interpreting the parts to be saved.
header_list = []
head_ctx = parse_tree.head()
if head_ctx:
for child in head_ctx.getChildren():
obj = dispatch(child, institution, row.requirement_id)
if obj != {}:
header_list.append(obj)
body_list = []
body_ctx = parse_tree.body()
if body_ctx:
for child in body_ctx.getChildren():
obj = dispatch(child, institution, row.requirement_id)
if obj != {}:
body_list.append(obj)
if update_db:
update_cursor.execute(
f"""
update requirement_blocks set header_list = %s, body_list = %s
where institution = '{row.institution}'
and requirement_id = '{row.requirement_id}'
""", (json.dumps(header_list), json.dumps(body_list)))
if period == 'current' or period == 'latest':
break
conn.commit()
conn.close()
return (header_list, body_list)
def _to_html(institution: str, discipline: str, course_dict: dict,
program_types, count_limit) -> str:
"""
"""
if institution and discipline and course_dict:
college = college_names[institution]
catalog_number = course_dict['catalog_number']
course_id = int(course_dict['course_id'])
title = course_dict['title']
conn = PgConnection()
cursor = conn.cursor()
cursor.execute(f"""
select r.*, b.block_type
from program_requirements r, requirement_blocks b
where r.id in (select program_requirement_id
from course_requirement_mappings
where course_id = {course_id})
and b.institution = '{institution}'
and b.requirement_id = r.requirement_id
""")
suffix = '' if cursor.rowcount == 1 else 's'
summary = (
f'<summary>{discipline} {catalog_number} ({course_id:06}) Satisfies '
f'{cursor.rowcount} requirement{suffix} at {college}</summary>')
body = """<table>
<tr>
<th>Requirement ID</th>
<th>Program</th>
<th>Requirement Name</th>
<th>Requirement</th>
<th>Alternatives</th>
<th>Context</th>
<th>Program Qualifiers</th>
<th>Requirement Qualifiers</th>
</tr>"""
skipped_majors = skipped_minors = skipped_concentrations = skipped_limit = 0
for row in cursor.fetchall():
if row.block_type in program_types and int(
row.course_alternatives) <= count_limit:
body += _format_requirement(row)
else:
if int(row.course_alternatives) > count_limit:
skipped_limit += 1
else:
# Count others only if not already skipped by the alternatives limit
if row.block_type == 'MAJOR':
skipped_majors += 1
if row.block_type == 'MINOR':
skipped_minors += 1
if row.block_type == 'CONC':
skipped_concentrations += 1
body += '</table>'
if (skipped_majors + skipped_minors + skipped_concentrations +
skipped_limit) > 0:
if count_limit < 999999:
suffix = '' if count_limit == 1 else 's'
body += (
f'<p>Skipped {skipped_limit} requirements with more than {count_limit} course '
f'alternative{suffix}.</p>')
situations = {
'major': (skipped_majors, '' if skipped_majors == 1 else 's'),
'minor': (skipped_minors, '' if skipped_minors == 1 else 's'),
'concentration': (skipped_concentrations,
'' if skipped_concentrations == 1 else 's')
}
for situation in situations.keys():
if situations[situation][0]: # number skipped
body += f'<p>Skipped {situations[situation][0]} {situation}{situations[situation][1]}</p>'
return f'<details open="open">{summary}<hr>{body}</details>'
else:
return '<p>Select a course to continue.</p>'
def dgw_parser(institution,
block_type,
block_value,
period='all',
do_parse=False):
""" For each matching Scribe Block, create a DGW_Processor to hold the info about it; the
constructor parses the block and extracts information objects from it, creating a HTML
representation of the Scribe Block and lists of dicts of the extracted objects, one for the
head and one for the body of the block.
Update/replace the HTML Scribe Block and the lists of object in the requirement_blocks table.
The period argument can be 'current', 'latest', or 'all', which will be picked out of the
result set for 'all'
"""
if DEBUG:
print(
f'*** dgw_parser({institution}, {block_type}, {block_value}. {period})',
file=sys.stderr)
if do_parse:
operation = 'Parsed'
else:
operation = 'Updated'
conn = PgConnection()
fetch_cursor = conn.cursor()
update_cursor = conn.cursor()
query = """
select requirement_id, title, period_start, period_stop, requirement_text
from requirement_blocks
where institution = %s
and block_type = %s
and block_value = %s
order by period_stop desc
"""
fetch_cursor.execute(query, (institution, block_type, block_value))
# Sanity Check
assert fetch_cursor.rowcount > 0, f'No Requirements Found\n{fetch_cursor.query}'
num_rows = fetch_cursor.rowcount
num_updates = 0
for row in fetch_cursor.fetchall():
if period == 'current' and row.period_stop != '99999999':
return f"""<h1 class="error">“{row.title}” is not a currently offered {block_type}
at {institution}.</h1>
"""
# Filter out everything after END.
# For parsing, also filter out "hide" things, but leave them in for display purposes.
text_to_parse = dgw_filter(row.requirement_text)
text_to_show = dgw_filter(row.requirement_text, remove_hide=False)
processor = DGW_Processor(institution, row.requirement_id, block_type,
block_value, row.title, row.period_start,
row.period_stop, text_to_show)
# Default behavior is just to show the scribe block(s), and not to try parsing them in real
# time. (But during development, that can be useful for catching coding errors.)
if do_parse:
if DEBUG:
print('Parsing ...', file=sys.stderr)
dgw_logger = DGW_Logger(institution, block_type, block_value,
row.period_stop)
input_stream = InputStream(text_to_parse)
lexer = ReqBlockLexer(input_stream)
lexer.removeErrorListeners()
lexer.addErrorListener(dgw_logger)
token_stream = CommonTokenStream(lexer)
parser = ReqBlockParser(token_stream)
parser.removeErrorListeners()
parser.addErrorListener(dgw_logger)
tree = parser.req_block()
try:
if DEBUG:
print('Walking ...', file=sys.stderr)
walker = ParseTreeWalker()
walker.walk(processor, tree)
except Exception as e:
exc_type, exc_value, exc_traceback = sys.exc_info()
print(f'{exc_type.__name__}: {exc_value}', file=sys.stderr)
traceback.print_tb(exc_traceback, limit=30, file=sys.stderr)
# msg_body = f"""College: {processor.institution}
# Block Type: {processor.block_type}
# Block Value: {processor.block_value}
# Catalog: {processor.catalog_years.catalog_type}
# Catalog Years: {processor.catalog_years.text}
# Error: {e}"""
requirement_html = re.sub(r'\n\s*', r'\n',
processor.html().replace("'", '’'))
head_objects = json.dumps(processor.sections[1])
body_objects = json.dumps(processor.sections[2])
# Add the info to the db
update_query = f""" update requirement_blocks
set requirement_html = '{requirement_html}',
head_objects = '{head_objects}',
body_objects = '{body_objects}'
where institution = '{institution}'
and requirement_id = '{row.requirement_id}'
"""
update_cursor.execute(update_query)
num_updates += update_cursor.rowcount
if DEBUG:
print(f'\r{operation} {institution} {row.requirement_id}', end='')
if period == 'current' or period == 'latest':
break
conn.commit()
conn.close()
if DEBUG:
print()
return (num_updates, num_rows)
latest = None
archives_dir = Path(
'/Users/vickery/Projects/CUNY_Programs/dgw_info/archives')
archives = archives_dir.glob('dgw_dap_req_block*.csv')
for archive in archives:
if latest is None or archive.stat().st_mtime > latest.stat(
).st_mtime:
latest = archive
if latest is None:
sys.exit(f'{file} does not exist, and no archive found.')
print(f'Using {latest}')
print('Clearing program requirements table (may take a while) ...', end='')
# Create the table
conn = PgConnection()
cursor = conn.cursor()
cursor.execute("""
drop table if exists requirement_blocks cascade;
create table requirement_blocks (
institution text not null,
requirement_id text not null,
block_type text,
block_value text,
title text,
period_start text,
period_stop text,
school text,
degree text,
college text,
major1 text,
major2 text,
from pgconnection import PgConnection
from collections import namedtuple
from dgw_parser import dgw_parser
trans_dict: Dict[int, Any] = dict()
for c in range(13, 31):
trans_dict[c] = None
trans_table = str.maketrans(trans_dict)
# Create dict of known colleges
colleges = dict()
course_conn = PgConnection()
course_cursor = course_conn.cursor()
course_cursor.execute(
'select substr(lower(code),0,4) as code, name from cuny_institutions')
for row in course_cursor.fetchall():
colleges[row.code] = row.name
if __name__ == '__main__':
# Command line args
parser = argparse.ArgumentParser(description='Test DGW Parser')
parser.add_argument('-d', '--debug', action='store_true', default=False)
parser.add_argument('-f', '--format')
parser.add_argument('-i', '--institutions', nargs='*', default=['QNS01'])
parser.add_argument('-t', '--types', nargs='+', default=['MAJOR'])
parser.add_argument('-v', '--values', nargs='+', default=['CSCI-BS'])
parser.add_argument('-a',