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,
}