def _get_local_notes_search_results(local_results, search_terms): results = [] student_rows = data_loch.get_basic_student_data( [row.get('sid') for row in local_results]) students_by_sid = {r.get('sid'): r for r in student_rows} for row in local_results: note = {camelize(key): row[key] for key in row.keys()} sid = note.get('sid') student_row = students_by_sid.get(sid, {}) results.append({ 'id': note.get('id'), 'studentSid': sid, 'studentUid': student_row.get('uid'), 'studentName': join_if_present( ' ', [student_row.get('first_name'), student_row.get('last_name')]), 'advisorUid': note.get('authorUid'), 'advisorName': note.get('authorName'), 'noteSnippet': _notes_text_snippet( join_if_present(' - ', [note.get('subject'), note.get('body')]), search_terms), 'createdAt': _isoformat(note, 'createdAt'), 'updatedAt': _isoformat(note, 'updatedAt'), }) return results
def _get_loch_notes_search_results(loch_results, search_terms): results = [] if not loch_results: return results sids = list( set([ row.get('advisor_sid') for row in loch_results if row.get('advisor_sid') is not None ])) calnet_advisor_feeds = get_calnet_users_for_csids(app, sids) for note in loch_results: advisor_feed = calnet_advisor_feeds.get(note.get('advisor_sid')) if advisor_feed: advisor_name = advisor_feed.get('name') or join_if_present( ' ', [ advisor_feed.get('first_name'), advisor_feed.get('last_name') ]) else: advisor_name = None note_body = (note.get('note_body') or '').strip() or join_if_present( ', ', [note.get('note_category'), note.get('note_subcategory')]) results.append({ 'id': note.get('id'), 'studentSid': note.get('sid'), 'studentUid': note.get('uid'), 'studentName': join_if_present(' ', [note.get('first_name'), note.get('last_name')]), 'advisorSid': note.get('advisor_sid'), 'advisorName': advisor_name or join_if_present(' ', [ note.get('advisor_first_name'), note.get('advisor_last_name') ]), 'noteSnippet': search_result_text_snippet(note_body, search_terms, TEXT_SEARCH_PATTERN), 'createdAt': resolve_sis_created_at(note), 'updatedAt': resolve_sis_updated_at(note), }) return results
def iter_csv(): def csv_line(_list): csv_output = io.StringIO() csv.writer(csv_output).writerow(_list) return csv_output.getvalue().encode('utf-8') csv_output.close() yield csv_line([ 'date_created', 'student_sid', 'student_name', 'author_uid', 'author_csid', 'author_name', 'subject', 'topics', 'attachments', 'body', 'late_change_request_action', 'late_change_request_status', 'late_change_request_term', 'late_change_request_course', ]) for note in notes: calnet_author = supplemental_calnet_advisor_feeds.get( note['author']['sid']) if calnet_author: calnet_author_name =\ calnet_author.get('name') or join_if_present(' ', [calnet_author.get('firstName'), calnet_author.get('lastName')]) calnet_author_uid = calnet_author.get('uid') else: calnet_author_name = None calnet_author_uid = None # strptime expects a timestamp without timezone; ancient date-only legacy notes get a bogus time appended. timestamp_created = f"{note['createdAt']}T12:00:00" if len( note['createdAt']) == 10 else note['createdAt'][:19] datetime_created = pytz.utc.localize( datetime.strptime(timestamp_created, '%Y-%m-%dT%H:%M:%S')) date_local = datetime_created.astimezone(app_timezone).strftime( '%Y-%m-%d') e_form = note.get('eForm') or {} yield csv_line([ date_local, sid, student_name, (note['author']['uid'] or calnet_author_uid), note['author']['sid'], (note['author']['name'] or calnet_author_name), note['subject'], '; '.join([t for t in note['topics'] or []]), '; '.join( [a['displayName'] for a in note['attachments'] or []]), note['body'], e_form.get('action'), e_form.get('status'), term_name_for_sis_id(e_form.get('term')), f"{e_form['sectionId']} {e_form['courseName']} - {e_form['courseTitle']} {e_form['section']}" if e_form.get('sectionId') else None, ])
def _get_loch_notes_search_results(loch_results, search_terms): results = [] calnet_advisor_feeds = get_calnet_users_for_csids( app, list( set([ row.get('advisor_sid') for row in loch_results if row.get('advisor_sid') is not None ])), ) for row in loch_results: note = {camelize(key): row[key] for key in row.keys()} advisor_feed = calnet_advisor_feeds.get(note.get('advisorSid')) advisor_name = join_if_present( ' ', [advisor_feed.get('firstName'), advisor_feed.get('lastName')]) if advisor_feed else None note_body = (note.get('noteBody') or '').strip() or join_if_present( ', ', [note.get('noteCategory'), note.get('noteSubcategory')]) results.append({ 'id': note.get('id'), 'studentSid': note.get('sid'), 'studentUid': note.get('uid'), 'studentName': join_if_present(' ', [note.get('firstName'), note.get('lastName')]), 'advisorSid': note.get('advisorSid'), 'advisorName': advisor_name or join_if_present( ' ', [note.get('advisorFirstName'), note.get('advisorLastName')]), 'noteSnippet': search_result_text_snippet(note_body, search_terms, NOTE_SEARCH_PATTERN), 'createdAt': _resolve_created_at(note), 'updatedAt': _resolve_updated_at(note), }) return results
def appointment_to_compatible_json(appointment, topics=(), attachments=None, event=None): # We have legacy appointments and appointments created via BOA. The following sets a standard for the front-end. advisor_sid = appointment.get('advisor_sid') advisor_uid = appointment.get('advisor_uid') appointment_id = appointment.get('id') appointment_type = appointment.get('appointment_type') cancelled = appointment.get('cancelled') departments = [] dept_codes = appointment.get('advisor_dept_codes') or [] created_by = appointment.get('created_by') or 'YCBM' for dept_code in dept_codes: departments.append({ 'code': dept_code, 'name': BERKELEY_DEPT_CODE_TO_NAME.get(dept_code, dept_code), }) api_json = { 'id': appointment_id, 'advisor': { 'id': AuthorizedUser.get_id_per_uid(advisor_uid) if advisor_uid else None, 'name': appointment.get('advisor_name') or join_if_present( ' ', [appointment.get('advisor_first_name'), appointment.get('advisor_last_name')], ), 'sid': advisor_sid, 'title': appointment.get('advisor_role'), 'uid': advisor_uid, 'departments': departments, }, 'appointmentTitle': appointment.get('title'), 'appointmentType': appointment_type, 'attachments': attachments, 'createdAt': resolve_sis_created_at(appointment) or appointment.get('starts_at').isoformat(), 'createdBy': created_by, 'deptCode': appointment.get('dept_code'), 'details': appointment.get('details'), 'endsAt': appointment.get('ends_at').isoformat() if created_by == 'YCBM' and appointment.get('ends_at') else None, 'student': { 'sid': appointment.get('student_sid'), }, 'topics': topics, 'updatedAt': resolve_sis_updated_at(appointment), 'updatedBy': appointment.get('updated_by'), 'cancelReason': appointment.get('cancellation_reason') if created_by == 'YCBM' else None, 'status': 'cancelled' if cancelled else None, } if appointment_type and appointment_type == 'Scheduled': api_json.update({ 'scheduledTime': _isoformat(appointment, 'scheduled_time'), 'studentContactInfo': appointment.get('student_contact_info'), 'studentContactType': appointment.get('student_contact_type'), }) if event: api_json.update(event) return api_json
def _get_local_notes_search_results(local_results, cutoff, search_terms): results = [] student_rows = data_loch.get_basic_student_data( [row.get('sid') for row in local_results]) students_by_sid = {r.get('sid'): r for r in student_rows} for row in local_results: note = {camelize(key): row[key] for key in row.keys()} sid = note.get('sid') student_row = students_by_sid.get(sid, {}) if student_row: omit_note_body = note.get( 'isPrivate') and not current_user.can_access_private_notes subject = note.get('subject') text = subject if omit_note_body else join_if_present( ' - ', [subject, note.get('body')]) results.append({ 'id': note.get('id'), 'studentSid': sid, 'studentUid': student_row.get('uid'), 'studentName': join_if_present(' ', [ student_row.get('first_name'), student_row.get('last_name') ]), 'advisorUid': note.get('authorUid'), 'advisorName': note.get('authorName'), 'noteSnippet': search_result_text_snippet(text, search_terms, TEXT_SEARCH_PATTERN), 'createdAt': _isoformat(note, 'createdAt'), 'updatedAt': _isoformat(note, 'updatedAt'), }) if len(results) == cutoff: break return results
def _get_loch_appointments_search_results(loch_results, search_terms): results = [] if not loch_results: return results sids = list(set([row.get('advisor_sid') for row in loch_results if row.get('advisor_sid') is not None])) calnet_advisor_feeds = get_calnet_users_for_csids(app, sids) for appointment in loch_results: advisor_feed = calnet_advisor_feeds.get(appointment.get('advisor_sid')) if advisor_feed: advisor_name = advisor_feed.get('name') or join_if_present(' ', [advisor_feed.get('firstName'), advisor_feed.get('lastName')]) else: advisor_name = None details = (appointment.get('note_body') or '').strip() or join_if_present( ', ', [appointment.get('note_category'), appointment.get('note_subcategory')], ) student_sid = appointment.get('sid') results.append({ 'id': appointment.get('id'), 'advisorName': advisor_name or join_if_present(' ', [appointment.get('advisor_first_name'), appointment.get('advisor_last_name')]), 'advisorRole': advisor_feed.get('title'), 'advisorUid': advisor_feed.get('uid'), 'advisorDeptCodes': [dept['code'] for dept in advisor_feed.get('departments')], 'createdAt': resolve_sis_created_at(appointment), 'details': details, 'detailsSnippet': search_result_text_snippet(details, search_terms, TEXT_SEARCH_PATTERN), 'studentSid': student_sid, 'updatedAt': resolve_sis_updated_at(appointment), 'student': { 'uid': appointment.get('uid'), 'firstName': appointment.get('first_name'), 'lastName': appointment.get('last_name'), 'sid': student_sid, }, }) return results
def search_advising_notes( search_phrase, author_uid=None, author_csid=None, student_csid=None, topic=None, datetime_from=None, datetime_to=None, offset=None, limit=None, ): if author_uid or author_csid: uid_author_filter = 'an.advisor_uid = :author_uid' if author_uid else None sid_author_filter = 'an.advisor_sid = :author_csid' if author_csid else None author_filter = 'AND (' + join_if_present( ' OR ', [uid_author_filter, sid_author_filter]) + ')' else: author_filter = '' sid_filter = 'AND an.sid = :student_csid' if student_csid else '' # Topic search is limited to SIS notes. if topic: topic_join = f"""JOIN {sis_advising_notes_schema()}.advising_note_topic_mappings antm ON antm.boa_topic = :topic JOIN {sis_advising_notes_schema()}.advising_note_topics ant ON ant.note_topic = antm.sis_topic AND ant.advising_note_id = an.id""" else: topic_join = '' date_filter = '' # We prefer to filter on updated_at, but that value is not meaningful for UCBCONVERSION notes. if datetime_from: date_filter += """ AND ((an.created_by = 'UCBCONVERSION' AND an.created_at >= :datetime_from) OR ((an.created_by != 'UCBCONVERSION' OR an.created_by IS NULL) AND an.updated_at >= :datetime_from))""" if datetime_to: date_filter += """ AND ((an.created_by = 'UCBCONVERSION' AND an.created_at < :datetime_to) OR ((an.created_by != 'UCBCONVERSION' OR an.created_by IS NULL) AND an.updated_at < :datetime_to))""" query_columns = """an.sid, an.id, an.note_body, an.advisor_sid, an.advisor_uid, an.created_by, an.created_at, an.updated_at, an.note_category, an.note_subcategory, sas.uid, sas.first_name, sas.last_name, an.advisor_first_name, an.advisor_last_name""" query_tables = f"""{advising_notes_schema()}.advising_notes an JOIN {student_schema()}.student_academic_status sas ON an.sid = sas.sid""" if search_phrase: query_columns += ", ts_rank(idx.fts_index, plainto_tsquery('english', :search_phrase)) AS rank" query_tables += f""" JOIN {advising_notes_schema()}.advising_notes_search_index idx ON idx.id = an.id AND idx.fts_index @@ plainto_tsquery('english', :search_phrase)""" else: query_columns += ', 0 AS rank' sql = f"""SELECT DISTINCT {query_columns} FROM {query_tables} {topic_join} WHERE TRUE {author_filter} {sid_filter} {date_filter} ORDER BY rank DESC, an.id""" if offset is not None and offset > 0: sql += ' OFFSET :offset' if limit is not None and limit < 150: # Sanity check large limits sql += ' LIMIT :limit' params = dict( search_phrase=search_phrase, author_csid=author_csid, author_uid=author_uid, student_csid=student_csid, topic=topic, datetime_from=datetime_from, datetime_to=datetime_to, offset=offset, limit=limit, ) return safe_execute_rds(sql, **params)
def put_notifications(student): sid = student['sid'] student['notifications'] = { 'note': [], 'alert': [], 'hold': [], 'requirement': [], } if app.config['FEATURE_FLAG_ADVISOR_APPOINTMENTS']: student['notifications']['appointment'] = [] for appointment in Appointment.get_appointments_per_sid(sid) or []: student['notifications']['appointment'].append({ **appointment.to_api_json(current_user.get_id()), **{ 'message': appointment.details, 'type': 'appointment', }, }) # The front-end requires 'type', 'message' and 'read'. Optional fields: id, status, createdAt, updatedAt. for note in get_advising_notes(sid) or []: message = note['body'] student['notifications']['note'].append({ **note, **{ 'message': message.strip() if message else None, 'type': 'note', }, }) for alert in Alert.current_alerts_for_sid(viewer_id=current_user.get_id(), sid=sid): student['notifications']['alert'].append({ **alert, **{ 'id': alert['id'], 'read': alert['dismissed'], 'type': 'alert', }, }) for row in get_sis_holds(sid): hold = json.loads(row['feed']) reason = hold.get('reason', {}) student['notifications']['hold'].append({ **hold, **{ 'createdAt': hold.get('fromDate'), 'message': join_if_present('. ', [ reason.get('description'), reason.get('formalDescription') ]), 'read': True, 'type': 'hold', }, }) degree_progress = student.get('sisProfile', {}).get('degreeProgress', {}) if degree_progress: for key, requirement in degree_progress.get('requirements', {}).items(): student['notifications']['requirement'].append( { **requirement, **{ 'type': 'requirement', 'message': requirement['name'] + ' ' + requirement['status'], 'read': True, }, })
def get_zip_stream_for_sid(sid): z = zipstream.ZipFile(mode='w', compression=zipstream.ZIP_DEFLATED) notes = get_advising_notes(sid) if not notes: return None filename = 'advising_notes' student_data = data_loch.get_basic_student_data([sid]) if student_data: student_row = student_data[0] student_name = join_if_present( ' ', [student_row.get('first_name'), student_row.get('last_name')]) filename = '_'.join([ filename, student_row.get('first_name', '').lower(), student_row.get('last_name', '').lower() ]) else: student_name = '' filename = '_'.join( [filename, localize_datetime(utc_now()).strftime('%Y%m%d')]) supplemental_calnet_advisor_feeds = get_calnet_users_for_csids( app, list( set([ note['author']['sid'] for note in notes if note['author']['sid'] and not note['author']['name'] ])), ) app_timezone = pytz.timezone(app.config['TIMEZONE']) def iter_csv(): def csv_line(_list): csv_output = io.StringIO() csv.writer(csv_output).writerow(_list) return csv_output.getvalue().encode('utf-8') csv_output.close() yield csv_line([ 'date_created', 'student_sid', 'student_name', 'author_uid', 'author_csid', 'author_name', 'subject', 'topics', 'attachments', 'body', 'late_change_request_action', 'late_change_request_status', 'late_change_request_term', 'late_change_request_course', ]) for note in notes: calnet_author = supplemental_calnet_advisor_feeds.get( note['author']['sid']) if calnet_author: calnet_author_name =\ calnet_author.get('name') or join_if_present(' ', [calnet_author.get('firstName'), calnet_author.get('lastName')]) calnet_author_uid = calnet_author.get('uid') else: calnet_author_name = None calnet_author_uid = None # strptime expects a timestamp without timezone; ancient date-only legacy notes get a bogus time appended. timestamp_created = f"{note['createdAt']}T12:00:00" if len( note['createdAt']) == 10 else note['createdAt'][:19] datetime_created = pytz.utc.localize( datetime.strptime(timestamp_created, '%Y-%m-%dT%H:%M:%S')) date_local = datetime_created.astimezone(app_timezone).strftime( '%Y-%m-%d') e_form = note.get('eForm') or {} yield csv_line([ date_local, sid, student_name, (note['author']['uid'] or calnet_author_uid), note['author']['sid'], (note['author']['name'] or calnet_author_name), note['subject'], '; '.join([t for t in note['topics'] or []]), '; '.join( [a['displayName'] for a in note['attachments'] or []]), note['body'], e_form.get('action'), e_form.get('status'), term_name_for_sis_id(e_form.get('term')), f"{e_form['sectionId']} {e_form['courseName']} - {e_form['courseTitle']} {e_form['section']}" if e_form.get('sectionId') else None, ]) z.write_iter(f'{filename}.csv', iter_csv()) all_attachment_filenames = set() all_attachment_filenames.add(f'{filename}.csv') for note in notes: for attachment in note['attachments'] or []: is_legacy_attachment = not is_int(attachment['id']) id_ = attachment['id'] if is_legacy_attachment else int( attachment['id']) stream_data = get_legacy_attachment_stream( id_) if is_legacy_attachment else get_boa_attachment_stream( id_) if stream_data: attachment_filename = stream_data['filename'] basename, extension = path.splitext(attachment_filename) suffix = 1 while attachment_filename in all_attachment_filenames: attachment_filename = f'{basename} ({suffix}){extension}' suffix += 1 all_attachment_filenames.add(attachment_filename) z.write_iter(attachment_filename, stream_data['stream']) return { 'filename': f'{filename}.zip', 'stream': z, }