def chair_departments(cid):
"""Returns all departments with which a chair/dean is associated."""
depts = OrderedDict()
base = """
SELECT
dept_table.dept as dept_code, dept_table.txt as dept_name,
dept_table.div as div_code, div_table.txt as div_name
FROM
dept_table
INNER JOIN
div_table ON dept_table.div = div_table.div
WHERE
CURRENT BETWEEN
dept_table.active_date
AND
NVL(dept_table.inactive_date, CURRENT)
AND
dept_table.web_display = "Y"
"""
sql = """
{0}
AND
div_table.head_id={1}
ORDER BY
dept_table.txt
""".format(base, cid)
rows = xsql(sql).fetchall()
if rows:
# division dean
dc = 'dean'
else:
# department chair
dc = 'chair'
sql = """
{0}
AND
dept_table.head_id={1}
AND
dept_table.dept != ("_ESN")
ORDER BY
dept_table.txt
""".format(base, cid)
rows = xsql(sql).fetchall()
if rows:
for row in rows:
depts[(row.dept_code)] = {
'dept_name': row.dept_name,
'dept_code': row.dept_code,
'div_name': row.div_name,
'div_code': row.div_code,
}
return ({'depts': depts}, dc, row.div_name, row.div_code)
else:
return ({'depts': depts}, None, None, None)
def _student(alert):
"""Obtain the student data."""
with get_connection() as connection:
student = xsql(VITALS(cid=alert.student.id), connection).fetchone()
obj = xsql(SPORTS(cid=alert.student.id), connection).fetchone()
sports = []
if obj and obj.sports:
athletics = obj.sports.split(',')
for s in SPORTS_ALL:
for a in athletics:
if a in s:
sports.append(s[1])
return {'student': student, 'sports': sports}
def get_userid(jenzabarUserID, username=False):
"""Obtains the user ID based on the UUID provided."""
ret = None
# prevent folks from submitting anything but the fwk_user id
#reggie="([\w\-\.]+[\-\.][\w\-\.]+)(\d+)\-(\w+)"
#reggie="(\{{0,1}([0-9a-fA-F]){8}-([0-9a-fA-F]){4}-([0-9a-fA-F]){4}-([0-9a-fA-F]){4}-([0-9a-fA-F]){12}\}{0,1})"
#reggie="([0-9a-fA-F]){8}(-([0-9a-fA-F]){4}){3}-([0-9a-fA-F]){12}"
#reggie ='^[A-Za-z0-9]{8}-[A-Za-z0-9]{4}-[A-Za-z0-9]{4}-[A-Za-z0-9]{4}-[A-Za-z0-9]{12}$'
# below works
reggie = '\w{8}-(\w{4}-){3}\w{12}'
# works
#reggie ='^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
# works
#reggie = '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'
pattern = re.compile(reggie)
if not pattern.match(jenzabarUserID):
return None
sql = "SELECT * FROM fwk_user WHERE id='{}'".format(jenzabarUserID)
# for SQLServer, you must use single quotes in the SQL incantation,
# otherwise it barfs for some reason
connection = get_connection(settings.MSSQL_EARL, encoding=False)
# automatically closes the connection after leaving 'with' block
with connection:
results = xsql(sql, connection, key='debug')
row = results.fetchone()
if username:
ret = row
else:
ret = row[5]
return ret
def get_cid(email):
"""Obtain a college ID from an email address."""
cid = None
obj = xsql(CID_FROM_EMAIL(email=email)).fetchone()
if obj:
cid = obj.id
return cid
def department_division_chairs(where):
"""Return the department chair and division dean profiles."""
rows = xsql(DEPARTMENT_DIVISION_CHAIRS(where=where))
try:
return rows.fetchall()
except AttributeError:
return None
def department_faculty(code, year):
"""Return the faculty for the department given the dept code & year."""
rows = xsql(DEPARTMENT_FACULTY(year=year, dept=code))
try:
return rows.fetchall()
except AttributeError:
return None
def person_departments(cid):
"""Returns all departments to which a person belongs."""
rows = xsql(PERSON_DEPARTMENTS(college_id=cid))
depts = []
for row in rows.fetchall():
depts.append((row.code.strip(), row.department.strip()))
return depts
def main():
"""Fetch the UUID from the database based on the email provided."""
sql = "SELECT * FROM fwk_user WHERE email='{}'".format(email)
connection = get_connection(settings.MSSQL_EARL, encoding=False)
with connection:
results = xsql(sql, connection, key='debug')
row = results.fetchone()
print(row)
def academic_department(did):
"""Returns academic departments based on department ID."""
sql = "{0} AND dept_table.dept = '{1}'".format(ACADEMIC_DEPARTMENTS, did)
rows = xsql(sql)
try:
return rows.fetchone()
except AttributeError:
return None
def department(code):
"""Returns the department given the three letter code."""
sql = "{0} AND hrdept = '{1}' ORDER BY DESCR".format(ALL_DEPARTMENTS, code)
rows = xsql(sql)
try:
return rows.fetchone()
except AttributeError:
return None
def get_uuid(email):
"""Obtains the UUID based on the provided email address."""
row = None
if validate_email(email):
sql = "SELECT * FROM fwk_user WHERE email='{}'".format(email)
connection = get_connection(settings.MSSQL_EARL, encoding=False)
with connection:
results = xsql(sql, connection)
row = results.fetchone()
return row
def main():
"""Obtain the data about who has two-factor authentication enabled."""
if who in {'faculty', 'staff'}:
where = '{0} IS NOT NULL'.format(who)
elif who == 'students':
where = 'student IS NOT NULL and student <> "incoming"'
else:
print("--who must be: 'faculty','staff', or 'students'")
exit(-1)
sql = """
SELECT
id, lastname, firstname,
(TRIM(username) || '@carthage.edu') as email
FROM
provisioning_vw
WHERE
{0}
ORDER BY
lastname, firstname
""".format(where)
report_man = ReportsManager(
scope='https://www.googleapis.com/auth/admin.reports.usage.readonly',
cache=cache)
total = 0
count = 0
email = None
with get_connection() as connection:
user_list = xsql(sql, connection)
for usr in user_list:
if usr[3] != email:
try:
user = report_man.user_usage(
email=usr[3], parameters='accounts:is_2sv_enrolled')
if user['usageReports'][0]['parameters'][0]['boolValue']:
count += 1
if test:
print("{0} {1}".format(
usr[3],
user['usageReports'][0]['parameters'][0]
['boolValue'],
))
total += 1
except Exception as error:
if test:
print("[error] {0} : {1}".format(usr[3], error))
else:
logger.info("fail: {0} {1}".format(usr[3], error))
email = usr[3]
if test:
print("{0} out of {1}".format(count, total))
def departments_all_choices():
"""Returns department tuples for choices parameter in models and forms."""
faculty = xsql(FACULTY_DEPTS)
staff = xsql(STAFF_DEPTS)
depts = [
('', '---Choose Your Department---'),
('', '---Faculty Departments---'),
]
if faculty:
for fac in faculty:
depts.append((fac.pcn_03.strip(), fac.department.strip()))
depts.append(('', '---Staff Deparments---'))
if staff:
for st in staff:
depts.append((st.hrdept.strip(), st.department.strip()))
return depts
def get_position(tpos):
"""
Obtains some user information based on job title position number.
NOTE: this is not very reliable when the position is vacant and/or
there is an interim appointment.
"""
key = 'TPOS_{0}'.format(tpos)
results = cache.get(key)
if not results:
sql = POSITION(tpos=tpos)
results = xsql(sql).fetchone()
if not results:
results = settings.TPOS_DEFAULT.get(tpos)
cache.set(key, results, None)
return results
def get_peeps(who):
"""Obtain the folks based on who parameter."""
key = 'provisioning_vw_{0}_api'.format(who)
peeps = cache.get(key)
if peeps is None:
if who == 'facstaff':
where = 'faculty IS NOT NULL OR staff IS NOT NULL'
elif who in ['faculty', 'staff', 'student']:
where = '{0} IS NOT NULL'.format(who)
else:
where = None
if not peeps and where:
sql = """
SELECT
id, lastname, firstname, username
FROM
provisioning_vw
WHERE
{0}
ORDER BY
lastname, firstname
""".format(where)
objects = xsql(sql, key=settings.INFORMIX_DEBUG)
if objects:
peeps = []
for obj in objects:
row = {
'cid': obj[0],
'lastname': obj[1],
'firstname': obj[2],
'email': '{0}@carthage.edu'.format(obj[3]),
'username': obj[3],
}
peeps.append(row)
cache.set(key, peeps, timeout=86400)
return peeps
def main():
"""Find deceased alumni."""
sql = '''
SELECT
*
FROM
profile_rec
WHERE
decsd_date IS NOT NULL
ORDER BY id
'''
if test:
print(sql)
with get_connection() as connection:
results = xsql(sql, connection, key='debug')
rows = results.fetchall()
for row in rows:
print(row)
def two_factor_auth(request):
groups = collections.OrderedDict()
sql = 'SELECT * FROM provisioning_vw WHERE '
groups['staff'] = '{0} staff IS NOT NULL'.format(sql)
groups['faculty'] = '{0} faculty IS NOT NULL'.format(sql)
groups['students'] = '{0} student IS NOT NULL'.format(sql)
report_man = ReportsManager(
scope='https://www.googleapis.com/auth/admin.reports.usage.readonly'
)
data = []
for n,v in groups.items():
sql = '{} ORDER BY email'.format(v)
total = 0
count = 0
email = None
with get_connection() as connection:
user_list = xsql(sql, connection)
for u in user_list:
if u.email != email:
try:
user = report_man.user_usage(
email=u.email,
parameters='accounts:is_2sv_enrolled'
)
if user['usageReports'][0]['parameters'][0]['boolValue']:
count += 1
total += 1
except Exception, e:
logger.info("{} fail: {}".format(n, email))
email = u.email
groups[n] = total
groups['{}_ave'.format(n)] = 100 * count / total
data.append(count)
data.append(total - count)
def team_manager(request, aid):
"""Manage team members."""
alert = get_object_or_404(Alert, pk=aid)
perms = alert.permissions(request.user)
student = _student(alert)
vitals = student['student']
team = [m.user for m in alert.team.all() if m.status]
matrix = []
for c in alert.category.all():
for m in c.matrix.all():
if m.user not in matrix and m.user not in team:
matrix.append(m.user)
advisor = None
eldap = LDAPManager()
if vitals:
try:
advisor = User.objects.get(pk=vitals.adv_id)
except User.DoesNotExist:
result_data = eldap.search(vitals.ldap_name.strip(), field='cn')
if result_data:
groups = eldap.get_groups(result_data)
advisor = eldap.dj_create(result_data, groups=groups)
if advisor and advisor not in matrix and advisor not in team:
matrix.append(advisor)
# check if we should add admissions reps and coaches to the matrix
admissions = False
admissions_group = 'Admissions Representative'
coaches = False
coaches_group = 'Coaches'
for c in alert.category.all():
for g in c.group.all():
if g.name == admissions_group:
admissions = True
if g.name == coaches_group:
coaches = True
# add admissions reps to the matrix
if admissions:
rep = None
group = Group.objects.get(name=admissions_group)
connection = get_connection()
with connection:
obj = xsql(ADMISSIONS_REP(cid=alert.student.id),
connection).fetchone()
if obj:
try:
rep = User.objects.get(pk=obj.id)
except:
luser = l.search(obj.id)
if luser:
rep = l.dj_create(luser)
if rep:
if not rep.groups.filter(name=admissions_group).exists():
group.user_set.add(rep)
if rep not in matrix and rep not in team:
matrix.append(rep)
# add coaches to the matrix
if coaches:
for c in User.objects.filter(groups__name=coaches_group):
if c not in matrix and c not in team:
matrix.append(c)
peeps = get_peeps('facstaff')
folks = team + matrix
# iterate over a copy of peeps and remove duplicates from original peeps
for p in peeps[:]:
for f in folks:
if f.id == p['cid']:
peeps.remove(p)
return render(
request,
'team.html',
{
'data': alert,
'perms': perms,
'matrix': matrix,
'return': True,
'student': vitals,
'sports': student['sports'],
'peeps': peeps,
},
)
def manager(request):
"""Manage object relationships for an Alert and for Alert values."""
user = request.user
data = {'msg': "Success", 'id': ''}
if request.is_ajax() and request.method == 'POST':
post = request.POST
# simple error handling to prevent malicious values
try:
oid = int(post.get('oid'))
aid = int(post.get('aid'))
except:
raise Http404("Invalid alert or object ID")
mod = post.get('mod')
alert = get_object_or_404(Alert, pk=aid)
action = post.get('action')
if mod == 'category':
obj = get_object_or_404(GenericChoice, pk=oid)
if action == 'add':
alert.category.add(obj)
elif action == 'remove':
alert.category.remove(obj)
else:
data['msg'] = "Options: add or remove"
elif mod == 'team':
try:
user = User.objects.get(pk=oid)
except User.DoesNotExist:
# chapuza because the LDAP attribute for user ID has a space
# in the name and we cannot search on it.
sql = 'SELECT * FROM cvid_rec WHERE cx_id={0}'.format(oid)
with get_connection() as connection:
cvid_rec = xsql(sql, connection).fetchone()
if cvid_rec:
username = cvid_rec.ldap_name.strip()
eldap = LDAPManager()
result_data = eldap.search(username, field='cn')
groups = eldap.get_groups(result_data)
user = eldap.dj_create(result_data, groups=groups)
else:
user = None
if user:
if action == 'add':
mail = False
if not alert.team.all() and alert.status != 'In progress':
alert.status = 'Assigned'
alert.save()
try:
member = Member.objects.get(user=user, alert=alert)
if member.status:
data['msg'] = "User is already a team member"
else:
member.status = True
member.save()
data['msg'] = "User has been reactivated"
data['id'] = member.id
mail = True
except Exception:
member = Member.objects.create(user=user, alert=alert)
alert.team.add(member)
data['msg'] = "User added to team"
data['id'] = member.id
mail = True
if mail:
to_list = [member.user.email]
bcc = [
settings.ADMINS[0][1],
]
if settings.DEBUG:
to_list = bcc
send_mail(
request,
to_list,
"Assignment to Outreach Team",
settings.CSS_FROM_EMAIL,
'alert/email_team_added.html',
{
'alert': alert,
'user': member.user
},
bcc,
)
elif action == 'remove':
member = get_object_or_404(Member, user=user, alert=alert)
member.status = False
member.case_manager = False
member.save()
else:
data['msg'] = "Options: add or remove"
else:
data['msg'] = "User not found"
elif mod == 'comment':
note = None
body = post.get('value')
t = loader.get_template('alert/annotation.inc.html')
if oid == 0:
if not alert.notes.all():
alert.status = 'In progress'
alert.save()
note = Annotation.objects.create(alert=alert,
created_by=user,
updated_by=user,
body=body,
tags='Comments')
alert.notes.add(note)
context = {'obj': note, 'bgcolor': 'bg-warning'}
data['msg'] = t.render(context, request)
else:
try:
note = Annotation.objects.get(pk=oid)
if action == 'fetch':
data['msg'] = note.body
elif action == 'delete':
note.delete()
else:
note.body = body
note.updated_by = user
note.save()
context = {'obj': note, 'bgcolor': 'bg-warning'}
data['msg'] = t.render(context, request)
data['id'] = note.id
except:
data['msg'] = "Follow-up not found"
elif mod == 'concern':
name = post.get('name')
data['id'] = aid
if action == 'fetch':
data['msg'] = getattr(alert, name)
else:
value = post.get('value')
# 'type of concern' / category is a list and m2m from alert
if name == 'category':
# disassociate the related objects
alert.category.clear()
# set the current relationships
for gid in post.getlist('value[]'):
gc = GenericChoice.objects.get(pk=gid)
alert.category.add(gc)
else:
setattr(alert, name, value)
alert.save()
if name in ['description', 'interaction_details']:
data[
'msg'] = '<div class="card-text" id="oid_{}_{}">{}</div>'.format(
name, aid, value)
else:
data['msg'] = 'Success'
else:
data['msg'] = "Invalid Data Model"
else:
data['msg'] = "Requires AJAX POST"
return HttpResponse(
json.dumps(data),
content_type='application/json; charset=utf-8',
)