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_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 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_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 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 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_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))
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)
if not latest.exists(): 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,
from io import StringIO 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'])