def main():
'''
main function
'''
session = get_session(EARL)
#...........................................
#print "drop temp table, just in case"
sql = "DROP TABLE ordered_terms"
try:
session.execute(sql)
print "ordered_terms table dropped"
except:
print "ordered_terms table not found"
sql = ORDERED_TERMS_TEMP
if test:
print sql
else:
session.execute(sql)
#...........................................
print "session details SQL"
sql = SESSION_DETAILS(
student_number = sid,
)
if test:
print sql
else:
details = session.execute(sql).first()
print details
session.close()
def test_student_balance_late_fee_sql(self):
session = get_session(self.earl)
# ordered terms temp table
sql = ORDERED_TERMS_TEMP(
start_date = settings.ORDERED_TERMS_START_DATE
)
session.execute(sql)
# latest terms temp table
sql = LATEST_TERM_TEMP
session.execute(sql)
# student balance late fee
sql = STUDENT_BALANCE_LATE_FEE(
student_number = self.sid
)
student = session.execute(sql).first()
self.assertEqual(
student.id, self.sid
)
session.close()
print student
def main():
"""
main function
"""
print "Student's college ID = {}".format(cid)
# create database session
session = get_session(EARL)
shi = session.query(StudentHealthInsurance).\
filter_by(college_id=cid).first()
print shi.__dict__
# empty the table for opt_out
if opt_out:
session.query(StudentHealthInsurance).\
filter_by(college_id=cid).\
update(STUDENT_HEALTH_INSURANCE)
session.commit()
print shi.__dict__
# test setting a date
#dob = datetime.strptime("1974-12-03", "%Y-%m-%d")
#shi.secondary_dob = dob
#session.commit()
#print shi.__dict__
session.close()
def create_item(item):
"""
Accepts a dictionary with the following keys:
course_number, title, year, term, fullname
"""
data = ITEM_METADATA
# create database session
session = get_session(EARL)
prefix = "UG"
if item["term"][0] == "G":
prefix = "GR"
cat = "{}{}".format(prefix, item["year"][-2:])
c = session.query(AbstractRecord).\
filter_by(crs_no = item["course_number"]).\
filter_by(cat = cat).first()
if c and c.abstr:
abstr = c.abstr.split('\n')
if len(abstr) > 1:
try:
abstr = abstr[2].decode("cp1252", "ignore")
except:
abstr = c.abstr.decode("cp1252", "ignore")
else:
abstr = c.abstr.decode("cp1252", "ignore")
else:
abstr = ""
dept = item["course_number"].split(" ")[0]
collection_id = DEPARTMENTS[dept]
# author
data['metadata'][0]['value'] = item["fullname"]
# description
data['metadata'][1]['value'] = abstr
# title
data['metadata'][2]['value'] = item["title"]
# title alternative
data['metadata'][3]['value'] = item["title_alt"]
# subject: year
data['metadata'][4]['value'] = item["year"]
# subject: term
data['metadata'][5]['value'] = TERM_LIST[item["term"]]
uri = "collections/{}/items".format(collection_id)
#print "data = {}".format(data)
#print "uri = {}".format(uri)
manager = Manager()
new_item = manager.request(uri, "post", data)
#print "new_item={}".format(new_item)
#print "id={}".format(new_item['id'])
return new_item
def student_balance_late_fee(request):
if request.POST:
# create database session
session = get_session(EARL)
# ordered terms temp table
sql = ORDERED_TERMS_TEMP(
start_date = settings.ORDERED_TERMS_START_DATE
)
session.execute(sql)
# latest terms temp table
session.execute(LATEST_TERM_TEMP)
# sa balances temp table
session.execute(SA_BALANCES_TEMP)
# pc balances temp table
session.execute(PC_BALANCES_TEMP)
# ca balances temp table
session.execute(CA_BALANCES_TEMP)
# ca1 balances temp table
session.execute(CA1_BALANCES_TEMP)
# wo balances temp table
session.execute(WO_BALANCES_TEMP)
# student balance late fee
students = session.execute(STUDENT_BALANCE_LATE_FEE)
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename={}.csv'.format(
'student_balance_late_fee'
)
t = loader.get_template('fee/student_balance_late_fee.txt')
context = {
'students': students,
}
response.write(t.render(context, request))
writer = csv.writer(response)
return response
return render(
request, 'fee/student_balance_late_fee.html'
)
def test_student_balance_late_fee(self):
print("\n")
print("Student balance late fee")
sid = settings.TEST_STUDENT_ID
seperator()
earl = reverse_lazy('student_balance_late_fee')
# get page
response = self.client.get(earl, follow=True)
self.assertEqual(response.status_code, 200)
# attempt to sign in with client login method
login = self.client.login(
username=self.user.username, password=self.password
)
self.assertTrue(login)
response = self.client.get(earl)
self.assertEqual(response.status_code, 200)
student = self.client.post(
earl, {'student_number': sid}
)
# create database session connection
session = get_session(self.earl)
# ordered terms temp table
sql = ORDERED_TERMS_TEMP(
start_date = settings.ORDERED_TERMS_START_DATE
)
session.execute(sql)
# latest terms temp table
sql = LATEST_TERM_TEMP
session.execute(sql)
# student balance late fee
sql = STUDENT_BALANCE_LATE_FEE(
student_number = self.sid
)
student = session.execute(sql).first()
self.assertEqual(
student.id, self.sid
)
session.close()
print student
def main():
"""
main function
"""
print "Student's college ID = {}".format(cid)
session = get_session(settings.INFORMIX_EARL)
student = session.query(Sicklecell).\
filter_by(college_id=cid).filter(\
(Sicklecell.proof == 1) | \
(Sicklecell.created_at > settings.START_DATE)\
).first()
print student.__dict__
def test_session_detail_sql(self):
session = get_session(self.earl)
sql = SESSION_DETAILS(
student_number = self.sid,
start_date = settings.ORDERED_TERMS_START_DATE
)
details = session.execute(sql).first()
self.assertEqual(
details.id, self.sid
)
session.close()
def test_trigger_grades_update(self):
print("\n")
print("trigger grades update informix")
print(seperator())
data = self.json_data
session = get_session(self.earl)
for grade in data['data']:
# 01234567890123456789012345678
# 2018;RC;FRN 2010;01;UG17;UNDG
# yr sess crs_no sec cat prog
school_code = grade['section_school_code']
# use the --debug-mode flag to test with SELECT
if settings.DEBUG:
sql = SELECT_GRADE(coursekey=school_code,
student_number=grade['school_uid'])
else:
sql = UPDATE_GRADE(grade=grade['updated_overall_grade'],
coursekey=school_code,
student_number=grade['school_uid'])
print("sql = {}".format(sql))
print("2018;RC;FRN 2010;01;UG17;UNDG")
print("yr sess crs_no sec cat prog")
print("cw_rec.yr = {}".format(school_code[0:4]))
print("cw_rec.cat = {}".format(school_code[20:25]))
print("cw_rec.sess = {}".format(school_code[5:7]))
print("cw_rec.prog = {}".format(school_code[25:]))
print("cw_rec.crs_no = {}".format(school_code[8:16]))
print("cw_rec.sec = {}".format(school_code[17:19]))
try:
response = session.execute(sql)
if settings.DEBUG:
for r in response:
print(r)
except:
logger.debug(
'test_trigger_grades_update_bad_data = {}'.format(grade))
session.close()
def home(request):
error = None
objects = None
if request.method == 'POST':
form = DetailCreatedForm(request.POST)
if form.is_valid():
data = form.cleaned_data
session = get_session(settings.INFORMIX_EARL)
objects = session.query(ProvisioningDetailRec).filter(
ProvisioningDetailRec.created_at >=
data['created_at'], ).all()
else:
form = DetailCreatedForm()
return render(request, 'dashboard/home.html', {
'form': form,
'objects': objects,
'error': error,
})
def main():
"""
main function
"""
# create database session
session = get_session(EARL)
print EARL
model = BASES[table]
objects = session.query(model).order_by(model.id).all()
count = 1
error = False
print "select all from table: {}".format(table)
for obj in objects:
if test:
try:
manager = session.query(StudentMedicalManager).\
filter_by(college_id=obj.college_id).one()
count += 1
except:
#pass
print "Error on manager with ID: {}".format(obj.college_id)
else:
try:
manager = session.query(StudentMedicalManager).\
filter_by(college_id=obj.college_id).one()
obj.manager_id = manager.id
count += 1
except exc.SQLAlchemyError as e:
print e
print obj
print count
error = True
session.rollback()
pass
print count
if not error and not test:
session.commit()
session.close()
def trigger_grades(request):
"""
Event trigger callback from API for updates to grades
"""
if request.method == 'POST':
data = json.loads(request.body)
session = get_session(settings.INFORMIX_EARL)
for grade in data['data']:
# 01234567890123456789012345678
# 2018;RC;FRN 2010;01;UG17;UNDG
# yr sess crs_no sec cat prog
try:
school_code = grade['section_school_code']
if settings.DEBUG:
sql = SELECT_GRADE(coursekey=school_code,
student_number=grade['school_uid'])
else:
sql = UPDATE_GRADE(grade=grade['updated_overall_grade'],
coursekey=school_code,
student_number=grade['school_uid'])
logger.debug("sql = {}".format(sql))
response = session.execute(sql)
except:
logger.debug('bad data = {}'.format(grade))
session.close()
msg = 'Success'
else:
# requires POST
msg = "Requires POST"
return HttpResponse(msg, content_type='text/plain; charset=utf-8')
def test_student_detail(self):
print("\n")
print("Student Detail")
sid = settings.TEST_STUDENT_ID
seperator()
earl = reverse('student_detail', args=[sid])
# get page
response = self.client.get(earl, follow=True)
self.assertEqual(response.status_code, 200)
# attempt to sign in with client login method
login = self.client.login(
username=self.username, password=self.password
)
self.assertTrue(login)
response = self.client.get(earl)
self.assertEqual(response.status_code, 200)
student = self.client.post(
earl, {'student_number': sid}
)
enrollment = get_objects(PROGRAM_ENROLLMENT(student_number=sid), True)
balances = get_objects(SUBSIDIARY_BALANCES(student_number=sid), True)
notes = get_objects(ACCOUNT_NOTES(student_number=sid), True)
session = get_session(self.earl)
sql = SESSION_DETAILS(
student_number = self.sid,
start_date = settings.ORDERED_TERMS_START_DATE
)
details = session.execute(sql).first()
self.assertEqual(enrollment.id, sid)
self.assertEqual(balances.id, sid)
if notes:
self.assertEqual(notes.id, sid)
self.assertEqual(details.id, sid)
def main():
"""
main function
"""
print EARL
session = get_session(EARL)
count = 1
if test:
cheques = session.query(Cheque).filter_by(jbstatus=STATUS).\
order_by(desc(Cheque.jbseqno))
for c in cheques:
print "{}) {}".format(count, c.__dict__)
count += 1
else:
rows = session.query(Cheque).filter_by(jbstatus=STATUS).\
delete(synchronize_session="fetch")
session.commit()
print "{} cheques deleted".format(rows)
# query
session.close()
def main():
"""
main function
"""
sql = """
SELECT
jbseqno, Min(ccreconjb_rec.jbimprt_date) AS crrntbatchdate
FROM
ccreconjb_rec
WHERE
jbimprt_date >= '{}'
GROUP BY jbseqno
""".format(import_date)
print EARL
session = get_session(EARL)
count = 1
objs = session.execute(sql)
for o in objs:
print "{}) {} {}".format(count, o.jbseqno, o.crrntbatchdate)
count += 1
# query
session.close()
def test_trigger_grades_update_bad_data(self):
print("\n")
print("trigger grades update informix with bad data")
print(seperator())
data = self.json_data_bad
session = get_session(self.earl)
for grade in data['data']:
try:
school_code = grade['section_school_code']
sql = UPDATE_GRADE(grade=grade['updated_overall_grade'],
coursekey=school_code,
student_number=grade['school_uid'])
print("update grade sql = {}".format(sql))
response = session.execute(sql)
except:
logger.debug(
'test_trigger_grades_update_bad_data = {}'.format(grade))
def main():
"""
main function
"""
# create database session
session = get_session(EARL)
model = BASES[table]
print("select all managers")
managers = session.query(StudentMedicalManager).all()
for man in managers:
try:
obj = session.query(model).filter_by(college_id=man.college_id).all()
if len(obj) > 1:
print("Manager ID {} has more than 1 rec: {}".format(
man.college_id, len(obj)
))
except:
pass
session.commit()
session.close()
def form(request, stype, wtype):
cid = request.user.id
# user student type and waiver type to build table name
table = 'cc_{}_{}_waiver'.format(stype, wtype)
# create database session
session = get_session(settings.INFORMIX_EARL)
# check for student manager record
manager = get_manager(session, cid)
# form name
fname = str_to_class(
'djsani.medical_history.waivers.forms',
'{}Form'.format(wtype.capitalize())
)
sicklecell = None
if wtype == 'sicklecell':
sicklecell = session.query(Sicklecell).\
filter_by(college_id=cid).filter(\
(Sicklecell.proof == 1) | \
(Sicklecell.created_at > settings.START_DATE)\
).first()
# check to see if they already submitted this form.
# redirect except for sicklecell waiver
# or wtype does not return a form class (fname)
if (manager and getattr(manager, table, None) and wtype != 'sicklecell') \
or not fname:
return HttpResponseRedirect( reverse_lazy('home') )
if request.method=='POST':
form = fname(request.POST, request.FILES)
if form.is_valid():
data = form.cleaned_data
# deal with file uploads
if request.FILES.get('results_file'):
folder = 'sicklecell/{}/{}'.format(
cid, manager.created_at.strftime('%Y%m%d%H%M%S%f')
)
p = join(settings.UPLOADS_DIR, folder)
phile = handle_uploaded_file(
request.FILES['results_file'], p
)
data['results_file'] = '{}/{}'.format(folder, phile)
if sicklecell:
# update student's sicklecell waiver record
data['updated_at'] = datetime.datetime.now()
for key, value in data.iteritems():
setattr(sicklecell, key, value)
else:
# insert
data['college_id'] = cid
data['manager_id'] = manager.id
model = str_to_class(
'djsani.medical_history.waivers.models',
wtype.capitalize()
)
s = model(**data)
session.add(s)
# update the manager
setattr(manager, table, True)
session.commit()
session.close()
return HttpResponseRedirect(
reverse_lazy('waiver_success')
)
else:
form = fname
session.close()
# check for a valid template or redirect home
try:
template = 'medical_history/waivers/{}_{}.html'.format(stype, wtype)
os.stat(os.path.join(settings.ROOT_DIR, 'templates', template))
except:
return HttpResponseRedirect( reverse_lazy('home') )
return render(
request, template,
{
'form':form,'next_year':NEXT_YEAR,'student':sicklecell
}
)
def get_students(request):
"""
GET or POST: returns a list of students
"""
trees=sport=None
term = get_term()
staff = in_group(request.user, STAFF)
coach = in_group(request.user, COACH)
if request.POST:
post = request.POST
if staff or coach:
# simple protection against sql injection
try:
sport = int(post.get('sport'))
except:
sport = 0
trees = post.get('print')
if sport and sport !=0 and staff and trees:
# print all athletes from any given sport
sql = ''' {}
WHERE stu_serv_rec.yr = "{}"
AND stu_serv_rec.sess = "{}"
AND cc_student_medical_manager.created_at > "{}"
AND cc_student_medical_manager.sports like "%{}%"
ORDER BY lastname
'''.format(
STUDENT_VITALS, term['yr'], term['sess'], settings.START_DATE,
str(sport)
)
template = 'dashboard/athletes_print.html'
else:
sql = ''' {}
AND stu_serv_rec.yr = "{}"
AND stu_serv_rec.sess = "{}"
'''.format(
STUDENTS_ALPHA, term['yr'], term['sess']
)
c = post.get('class')
if c in ['0','1','2','3','4']:
if c == '1':
sql += 'AND cc_student_medical_manager.sitrep = 1'
elif c == '0':
sql += 'AND cc_student_medical_manager.sitrep = 0'
elif c == '3':
sql += 'AND cc_student_medical_manager.athlete = 1'
elif c == '4':
sql += 'AND cc_student_health_insurance.primary_policy_type="Gov"'
else:
sql += 'AND cc_student_medical_manager.id IS NULL'
else:
sql += 'AND prog_enr_rec.cl IN ({})'.format(c)
if sport and sport != 0:
sql += '''
AND cc_student_medical_manager.sports like "%{}%"
'''.format(str(sport))
sql += ' ORDER BY lastname'
template = 'dashboard/students_data.inc.html'
else:
return HttpResponse("error", content_type="text/plain; charset=utf-8")
else:
template = 'dashboard/home.html'
sql = ''' {}
AND stu_serv_rec.yr = "{}"
AND stu_serv_rec.sess = "{}"
AND prog_enr_rec.cl IN ("FN","FF","FR","UT","PF","PN")
ORDER BY lastname
'''.format(
STUDENTS_ALPHA, term['yr'], term['sess']
)
objs = do_esql(
sql, key=settings.INFORMIX_DEBUG, earl=EARL
)
students = None
if objs:
session = get_session(EARL)
students = [dict(row) for row in objs.fetchall()]
for s in students:
adult = 'minor'
if s['birth_date']:
age = calculate_age(s['birth_date'])
if age > 17:
adult = 'adult'
s['adult'] = adult
if trees:
manager = get_manager(session, s['id'])
# emergency notification system
s['ens'] = session.query(AARec).filter_by(id=s['id']).\
filter(AARec.aa.in_(ENS_CODES)).all()
# health insurance
s['shi'] = panels(
request, session, StudentHealthInsurance, manager
)
return render(
request, template, {
'students':students,'sports':SPORTS,'sport':sport,'staff':staff,
'coach':coach
}
)
def student_detail(request, cid=None, medium=None, content=None):
"""
main method for displaying student data
"""
if in_group(request.user, STAFF):
term = get_term()
template = 'dashboard/student_detail.html'
if content:
template = 'dashboard/student_{}_{}.html'.format(
medium, content
)
my_sports = None
manager = None
session = get_session(EARL)
# search form, grab only numbers from string
if not cid:
cid = filter(str.isdigit, str(request.POST.get('cid')))
# get all managers for switch select options
managers = session.query(StudentMedicalManager).\
filter_by(college_id=cid).all()
# we do not want to display faculty/staff details
# nor do we want to create a manager for them
if cid and not faculty_staff(cid):
# manager ID comes from profile switcher POST from form
manid = request.POST.get('manid')
# or from URL with GET variable
if not manid:
manid = request.GET.get('manid')
# get student
if manid:
sql = '''
{} WHERE cc_student_medical_manager.id = {}
ORDER by stu_serv_rec.stusv_no DESC
'''.format(STUDENT_VITALS, manid)
else:
sql = '''
{} WHERE id_rec.id = "{}"
ORDER BY cc_student_medical_manager.created_at DESC
'''.format(STUDENT_VITALS, cid)
obj = do_esql(sql, key=settings.INFORMIX_DEBUG, earl=EARL)
if obj:
student = obj.fetchone()
if student:
if manid:
manager = session.query(StudentMedicalManager).\
filter_by(id=manid).one()
if not manager:
manager = get_manager(session, cid)
# execute student vitals sql again in case we just created
# a new manager
obj = do_esql(sql, key=settings.INFORMIX_DEBUG, earl=EARL)
student = obj.fetchone()
# calculate student's age
try:
age = calculate_age(student.birth_date)
except:
age = None
# emergency notification system
ens = session.query(AARec).filter_by(id=cid).\
filter(AARec.aa.in_(ENS_CODES)).all()
# health insurance
shi = panels(
request,session,StudentHealthInsurance,manager,content
)
# student medical history
smh = panels(
request,session,StudentMedicalHistory,manager,content,
student.sex
)
# athlete medical history
amh = panels(
request,session,AthleteMedicalHistory,manager,content,
student.sex
)
# used for staff who update info on the dashboard
stype = 'student'
if student.athlete:
stype = 'athlete'
if student.sports:
my_sports = student.sports.split(',')
if student.sex == 'F':
sports = SPORTS_WOMEN
else:
sports = SPORTS_MEN
try:
student_user = User.objects.get(pk=cid)
except:
student_user = None
else:
age=ens=shi=smh=amh=student=sports=stype=student_user=manager=None
return render(
request, template,
{
'student':student,'student_user':student_user,'age':age,
'ens':ens, 'shi':shi,'amh':amh,'smh':smh,'cid':cid,
'switch_earl':reverse_lazy('set_val'),
'sports':sports,'my_sports':my_sports,
'next_year':NEXT_YEAR,'stype':stype,'managers':managers,
'manager':manager,'MedicalStaff':True
}
)
else:
raise Http404
else:
raise Http404
else:
return HttpResponseRedirect(reverse_lazy('access_denied'))
def main():
"""
main function
"""
# convert date to datetime
import_date = datetime.strptime(date, "%Y-%m-%d")
print "import_date = {}".format(import_date)
# for some reason we set jbpayee equal to the import date
# plus user info
jbpayee = "{}_{}".format(
TODAY, settings.ADMINS[0][0]
)
# CSV headers
fieldnames = (
"jbstatus_date", "jbstatus", "jbamount",
"jbaccount", "jbchkno", "jbpayee"
)
# remove all lines up to and including the headers line
with open(phile, "r") as f:
n = 0
for line in f.readlines():
n += 1
if 'As of date' in line: # line in which field names was found
break
f.close()
f = islice(open(phile, "r"), n, None)
# read the CSV file
reader = csv.DictReader(f, fieldnames, delimiter=',')
# create database session
if test:
print EARL
print settings.IMPORT_STATUS
session = get_session(EARL)
session.autoflush = False
x = 0
for r in reader:
# convert amount from string to float and strip dollar sign
try:
jbamount = float(r["jbamount"][1:].replace(',',''))
except:
jbamount = 0
# status date
try:
jbstatus_date = datetime.strptime(
r["jbstatus_date"], "%m/%d/%Y"
)
except:
jbstatus_date = None
# check number
try:
cheque_number = int(r["jbchkno"])
except:
cheque_number = 0
# create a Cheque object
cheque = Cheque(
jbimprt_date=import_date,
jbstatus_date=jbstatus_date,
jbchkno=cheque_number, jbchknolnk=cheque_number,
jbstatus=settings.IMPORT_STATUS, jbaction="",
jbaccount=r["jbaccount"], jbamount=jbamount,
jbamountlnk=jbamount, jbpayee=jbpayee
)
# missing fields: jbissue_date, jbpostd_dat
if test:
print "{}) {}".format(x, cheque.__dict__)
else:
# insert the data
try:
session.add(cheque)
session.flush()
except exc.SQLAlchemyError as e:
print e
print "Bad data: {}".format(cheque.__dict__)
session.rollback()
x += 1
if not test:
session.commit()
# fin
print "Checks processed: {}".format(x)
session.close()
def home(request):
if settings.ACADEMIC_YEAR_LIMBO:
return render(
request, 'closed.html',
)
# for when faculty/staff sign in here or not student found
data = {}
# create database session
session = get_session(EARL)
user = request.user
# fetch college id from user object
cid = user.id
# retrieve student manager (or create a new one if none exists)
manager = get_manager(session, cid)
# intialise some things
my_sports = ''
student = None
adult = False
# get academic term
term = get_term()
# get student
sql = ''' {}
WHERE
id_rec.id = "{}"
AND stu_serv_rec.yr = "{}"
AND UPPER(stu_serv_rec.sess) = "{}"
AND cc_student_medical_manager.created_at > "{}"
'''.format(
STUDENT_VITALS, cid, term['yr'], term['sess'], settings.START_DATE
)
engine = get_engine(EARL)
obj = engine.execute(sql)
student = obj.fetchone()
if student:
# save some things to Django session:
request.session['gender'] = student.sex
# sports needs a python list
if manager.sports:
my_sports = manager.sports.split(',')
# adult or minor? if we do not have a DOB, default to minor
if student.birth_date:
age = calculate_age(student.birth_date)
if age >= 18:
adult = True
# show the corresponding list of sports
if student.sex == 'F':
sports = SPORTS_WOMEN
else:
sports = SPORTS_MEN
# quick switch for minor age students
if request.GET.get('minor'):
adult = False
# context dict
data = {
'switch_earl': reverse_lazy('set_val'),
'student':student,
'manager':manager,
'sports':sports,
'my_sports':my_sports,
'adult':adult,'sql':sql
}
# emergency contact modal form
sql = 'SELECT * FROM aa_rec WHERE aa in {} AND id="{}"'.format(
ENS_CODES, cid
)
objs = session.execute(sql)
for o in objs:
row = {}
for field in ENS_FIELDS:
try:
value = getattr(o, field).decode('cp1252').encode('utf-8')
except:
value = getattr(o, field)
row[field] = value
data[o.aa] = row
data['mobile_carrier'] = MOBILE_CARRIER
data['relationship'] = RELATIONSHIP
data['solo'] = True
else:
if not in_group(user, 'carthageStaffStatus') and \
not in_group(user, 'carthageFacultyStatus'):
# could not find student by college_id
data = {
'student':student,'sports':SPORTS,'solo':True,'adult':adult
}
# notify managers
send_mail(
request, settings.HOUSING_EMAIL_LIST,
u'[Lost] Student: {} {} ({})'.format(
user.first_name, user.last_name, cid
), user.email, 'alert_email.html', request,
[settings.MANAGERS[0][1],]
)
session.close()
return render(request, 'home.html', data)
def cheque_data(request):
"""
Form that allows the user to upload bank data in CSV format
and then inserts the data into the database
"""
# lists for recording results
data = None
cheques = []
fail = []
uid = request.GET.get('uid')
if request.method=='POST':
form = ChequeDataForm(request.POST, request.FILES)
if form.is_valid():
# database connection
session = get_session(EARL)
session.autoflush = False
# convert date to datetime
import_date = datetime.combine(
form.cleaned_data['import_date'], datetime.min.time()
)
# for some reason we set jbpayee equal to the import date
# plus user info
jbpayee = '{}_{}'.format(
form.cleaned_data['import_date'], 'business_office'
)
# CSV headers
fieldnames = (
'jbstatus_date', 'jbstatus', 'jbamount',
'jbaccount', 'jbchkno', 'jbpayee'
)
# obtain the CSV file from POST and upload
phile = handle_uploaded_file(request.FILES['bank_data'])
# remove all lines up to and including the headers line
with open(phile, 'r') as f:
n = 0
for line in f.readlines():
n += 1
# line in which field headers live
if 'As of date' in line:
break
f.close()
f = islice(open(phile, 'r'), n, None)
# read the CSV file
reader = csv.DictReader(f, fieldnames, delimiter=',')
# for each line create a Cheque object
for r in reader:
# convert amount from string to float and strip dollar sign
try:
jbamount = float(r['jbamount'][1:].replace(',',''))
except:
jbamount = 0
# status date
try:
jbstatus_date = datetime.strptime(
r['jbstatus_date'], '%m/%d/%Y'
)
except:
jbstatus_date = None
# check number
try:
cheque_number = int(r['jbchkno'])
except:
cheque_number = 0
# create a Cheque object
cheque = Cheque(
jbimprt_date=import_date,
jbstatus_date=jbstatus_date,
jbchkno=cheque_number, jbchknolnk=cheque_number,
jbstatus=settings.IMPORT_STATUS, jbaction='',
jbaccount=r['jbaccount'], jbamount=jbamount,
jbamountlnk=jbamount, jbpayee=jbpayee
)
# insert the data
try:
session.add(cheque)
session.flush()
cheques.append(cheque.__dict__)
except exc.SQLAlchemyError as e:
fail.append(cheque.__dict__)
session.rollback()
# execute the reconciliation process
data = recce_cheques(request, session, import_date)
# commit the reconciliation updates
session.commit()
rsvp = render(request, 'reconciliation/data_form.html', {
'form':form, 'earl':EARL, 'fail':fail,
'cheques':cheques, 'data':data
}
)
# done
session.close()
return rsvp
else:
form = ChequeDataForm()
return render(request, 'reconciliation/data_form.html', {
'form':form,'earl':EARL,'uid':uid
}
)
def main():
'''
First generation align. Aligns adm_rec with FAFSA information.
'''
# create database connection
session = get_session(EARL)
if test:
print "database connection URL = {}".format(EARL)
#...........................................
# drop temp table, just in case
drop1 = 'DROP TABLE adm_records'
if test:
print drop1
try:
session.execute(drop1)
print "adm_records dropped"
except:
print "no temp table: adm_records"
Sel1SQL = '''
SELECT
app_no, id
FROM
adm_rec
WHERE
primary_app = 'Y'
AND
plan_enr_sess = 'RA'
AND
plan_enr_yr = 2017
AND
prog = 'UNDG'
AND
subprog = 'TRAD'
INTO TEMP
adm_records
'''
if test:
print Sel1SQL
session.execute(Sel1SQL)
#...........................................
# drop temp table, just in case
drop2 = 'DROP TABLE naf_records'
if test:
print drop2
try:
session.execute(drop2)
print "naf_records dropped"
except:
print "no temp table: naf_records"
Sel2SQL = '''
SELECT
id,
CASE
WHEN
naf1718_rec.dad_educ = '3' THEN 'N'
WHEN
naf1718_rec.mom_educ = '3' THEN 'N'
ELSE
'Y'
END AS
first_generation
FROM
naf1718_rec
WHERE
method = 'FM'
INTO TEMP
naf_records
'''
if test:
print Sel2SQL
session.execute(Sel2SQL)
Sel3SQL = '''
SELECT
app_no, first_generation
FROM
adm_records, naf_records
WHERE
adm_records.id = naf_records.id
'''
if test:
print Sel3SQL
results = session.execute(Sel3SQL)
for row in results:
updSQL = '''
UPDATE
adm_rec
SET
first_gen == {}
WHERE
app_no == {}
AND
first_gen <> {}
'''.format(row[1], row[0], row[1])
if not test:
try:
session.execute(updSQL)
except Exception, e:
print "Update failed"
print "Exception: {}".format(str(e))
else:
print updSQL
def setUp(self):
self.session = get_session(EARL)
from django.conf import settings from djzbar.utils.informix import get_session from djsani.core.utils import get_manager EARL = settings.INFORMIX_EARL session = get_session(EARL) cid = obj = get_manager(session,cid) print obj.__dict__ print obj.status
def set_val(request):
"""
Ajax POST for to set a single name/value pair, used mostly for
jquery xeditable and ajax updates for student medical manager.
Requires via POST:
college_id
name (database field)
value
pk (primary key of object to be updated)
table
"""
staff = in_group(request.user, settings.STAFF_GROUP)
# we need a table name
table = request.POST.get('table')
if not table:
return HttpResponse("Error: no table name")
# we need a college ID to insure no funny stuff
cid = request.POST.get('college_id')
if not cid:
return HttpResponse("Error: no college ID")
elif not staff and int(cid) != request.user.id:
return HttpResponse("Not staff")
else:
# name/value pair
name = request.POST.get('name')
# sports field is a list
if name == 'sports':
value = ','.join(request.POST.getlist('value[]'))
else:
value = request.POST.get('value')
# primary key
pk = request.POST.get('pk')
# create our dictionary to hold name/value pairs
dic = { name: value }
if table == 'cc_athlete_sicklecell_waiver':
# set value = 1 if field name = 'waive'or
# if it = 'results' since that value is
# either Positive or Negative
if name == 'results':
dic['proof'] = 1
dic['waive'] = 0
value = 1
elif name == 'waive':
dic['proof'] = 0
dic['waive'] = value
dic['results'] = ''
elif name == 'proof':
dic['results'] = ''
# create database session
session = get_session(EARL)
# retrieve student manager
man = get_manager(session, cid)
if WAIVERS.get(table) and not pk:
# create new waiver
dic['college_id'] = cid
dic['manager_id'] = man.id
obj = WAIVERS[table](**dic)
session.add(obj)
# update the manager
setattr(man, table, value)
session.flush()
else:
model = BASES[table]
obj = session.query(model).\
filter_by(id=pk).first()
if not obj:
return HttpResponse(
"No object found associated with ID: {}".format(pk),
content_type='text/plain; charset=utf-8'
)
else:
if name == 'athlete' and str(value) == '0':
dic['sports'] = ''
# green check mark for athletes
if name == 'sitrep' and str(value) == '1':
if obj.medical_consent_agreement:
dic['medical_consent_agreement_status'] = 1
if obj.physical_evaluation_1:
dic['physical_evaluation_status_1'] = 1
if obj.physical_evaluation_2:
dic['physical_evaluation_status_2'] = 1
# update existing object
for key, value in dic.iteritems():
setattr(obj, key, value)
session.flush()
# if waiver, update manager table
if WAIVERS.get(table):
setattr(man, table, value)
# update the log entry for staff modifications
if staff:
message = ''
for n,v in dic.items():
message += u'{} = {}\n'.format(n,v)
log = {
'college_id': request.user.id,
'content_type_id': get_content_type(session, table).id,
'object_id': obj.id,
'object_repr': '{}'.format(obj),
'action_flag': CHANGE,
'action_message': message
}
log_entry = StudentMedicalLogEntry(**log)
session.add(log_entry)
session.commit()
session.close()
return HttpResponse(
"success", content_type='text/plain; charset=utf-8'
)
def setUp(self):
self.session = get_session(EARL)
self.created_at_date = settings.TEST_CREATED_AT_DATE
def main():
"""
main function
"""
# start time
print datetime.now()
# convert date to datetime
import_date = datetime.strptime(date, "%Y-%m-%d")
print "import_date = {}".format(import_date)
# create database connection
session = get_session(EARL)
print "database connection URL = {}".format(EARL)
#...........................................
print "drop temp tables, just in case"
sql = "DROP TABLE tmp_voida"
print sql
if not test:
try:
session.execute(sql)
print "tmp_voida dropped"
except:
print "no temp table: tmp_voida"
#...........................................
sql = "DROP TABLE tmp_voidb"
print sql
if not test:
try:
session.execute(sql)
print "tmp_voidb dropped"
except:
print "no temp table: tmp_voidb"
#...........................................
print "Populate tmp_voida temp table"
print "TMP_VOID_A sql:"
sql = TMP_VOID_A
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST: select * from tmp_voida table and print"
print "SELECT_VOID_A sql:"
sql = SELECT_VOID_A
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "Populate tmp_voidb temp table"
print "TMP_VOID_B sql:"
sql = TMP_VOID_B
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "select * from tmp_voidb. print here / send_mail() in the view"
print "SELECT_VOID_B sql:"
sql = SELECT_VOID_B
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "set reconciliation status to 'v'"
print "UPDATE_RECONCILIATION_STATUS sql:"
sql = UPDATE_RECONCILIATION_STATUS
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST sql:"
sql = "SELECT * FROM gltr_rec WHERE recon_stat = '{}'".format(
settings.REQUI_VICH
)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
print "Find the duplicate check numbers and update as 's'uspicious"
#...........................................
print "first, drop the temp tables, just in case. sql:"
sql = "DROP TABLE tmp_maxbtchdate"
print sql
if not test:
try:
session.execute(sql)
print "tmp_maxbtchdate dropped"
except:
print "no temp table: tmp_maxbtchdate"
#...........................................
sql = "DROP TABLE tmp_DupCkNos"
print sql
if not test:
try:
session.execute(sql)
print "tmp_DupCkNos dropped"
except:
print "no temp table: tmp_DupCkNos"
#...........................................
sql = "DROP TABLE tmp_4updtstatus"
print sql
#if not test:
try:
session.execute(sql)
print "tmp_4updtstatus dropped"
except:
print "no temp table: tmp_4updtstatus"
#...........................................
print "select import_date and stick it in a temp table, for some reason"
print "SELECT_CURRENT_BATCH_DATE sql:"
sql = SELECT_CURRENT_BATCH_DATE(import_date=import_date)
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST: display batch date. sql:"
sql = "SELECT * FROM tmp_maxbtchdate"
print sql
if not test:
obj = session.execute(sql)
print obj.fetchone().crrntbatchdate
#...........................................
print "Select the duplicate cheques"
print "SELECT_DUPLICATES_1 sql:"
sql = SELECT_DUPLICATES_1(import_date=import_date)
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST: print selected duplicate cheques. sql:"
sql = "SELECT * FROM tmp_dupcknos"
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "Select for updating"
print "SELECT_FOR_UPDATING sql:"
sql = SELECT_FOR_UPDATING(
import_date=import_date,
status=settings.IMPORT_STATUS
)
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "Select the records for update. print here / send_mail() in the view"
print "SELECT_RECORDS_FOR_UPDATE sql:"
sql = SELECT_RECORDS_FOR_UPDATE
print sql
if not test:
objs = session.execute(SELECT_RECORDS_FOR_UPDATE).fetchall()
for o in objs:
print o
#...........................................
print "Update cheque status to 's'uspictious"
print "UPDATE_STATUS_SUSPICIOUS sql:"
sql = UPDATE_STATUS_SUSPICIOUS
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST sql:"
sql = "SELECT * FROM ccreconjb_rec WHERE jbstatus = '{}'".format(
settings.SUSPICIOUS
)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "Select the duplicates. print here / send_mail() in the view"
print "SELECT_DUPLICATES_2 sql:"
sql = SELECT_DUPLICATES_2(import_date=import_date)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
print "Find the cleared CheckNos and update gltr_rec as 'r'econciled"
print "and ccreconjb_rec as 'ar' (auto-reconciled)"
#...........................................
print "Drop the temporary table, just in case. sql:"
sql = "DROP TABLE tmp_reconupdta"
print sql
if not test:
try:
session.execute(sql)
print "tmp_reconupdta dropped"
except:
print "no temp table: tmp_reconupdta"
#...........................................
print "Find the cleared Check Numbers"
print "SELECT_CLEARED_CHEQUES sql:"
sql = SELECT_CLEARED_CHEQUES(
import_date=import_date,
suspicious=settings.SUSPICIOUS,
auto_rec=settings.AUTO_REC,
requi_rich=settings.REQUI_RICH,
requi_vich=settings.REQUI_VICH
)
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST: print selected duplicate cheques. sql:"
sql = "SELECT * FROM tmp_reconupdta"
print sql
if not test:
objs = session.execute("SELECT * FROM tmp_reconupdta").fetchall()
for o in objs:
print o
#...........................................
print "Set gltr_rec as 'r'econciled"
print "UPDATE_RECONCILED sql:"
sql = UPDATE_RECONCILED
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST sql:"
sql = "SELECT * FROM gltr_rec WHERE recon_stat = '{}'".format(
settings.REQUI_RICH
)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "Set ccreconjb_rec as 'ar' (auto-reconciled)"
print "UPDATE_STATUS_AUTO_REC sql:"
sql = UPDATE_STATUS_AUTO_REC
if not test:
x = session.execute(sql)
print x.context.statement
else:
print sql
#...........................................
print "TEST sql:"
sql = "SELECT * FROM ccreconjb_rec where jbstatus = '{}'".format(
settings.AUTO_REC
)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
print "select the reconciled checks. print here / send_mail() in view"
print "SELECT_RECONCILIATED sql:"
sql = SELECT_RECONCILIATED
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "Display any left over imported checks whose status has not changed"
print "SELECT_REMAINING_EYE sql:"
sql = SELECT_REMAINING_EYE
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#...........................................
print "This selects the non-reconciled import records and finds"
print "the CX original transaction"
print "SELECT_NON_RECONCILDED sql:"
sql = SELECT_NON_RECONCILDED(
import_date=import_date,
suspicious=settings.SUSPICIOUS,
status=settings.IMPORT_STATUS
)
print sql
if not test:
objs = session.execute(sql).fetchall()
for o in objs:
print o
#session.commit()
session.close()
# end time
print datetime.now()
def main():
"""
main function
"""
key = settings.INFORMIX_DEBUG
if filetype not in ['csv', 'xlsx']:
if test:
print("filetype must be: 'csv' or 'xlsx'\n")
parser.print_help()
else:
info_logger.info("filetype must be: 'csv' or 'xlsx'")
exit(-1)
if database == 'train':
EARL = INFORMIX_EARL_TEST
elif database == 'cars':
EARL = INFORMIX_EARL_PROD
else:
if test:
print("database must be: 'cars' or 'train'\n")
parser.print_help()
else:
info_logger.info("database must be: 'cars' or 'train'")
exit(-1)
sql = SELECT_NEW_PEOPLE(where='')
if test:
debug_logger.debug("new people sql")
debug_logger.debug("sql = {}".format(sql))
people = []
objects = do_sql(sql, key=key, earl=EARL)
# the people list allows us to iterate over the result set more
# than once, whereas just using objects result would throw an
# error after the first iteration.
for o in objects:
if test:
debug_logger.debug(o)
else:
info_logger.info("o = {}".format(o))
# we need a username to proceed
if o.loginid:
people.append(o)
else:
provisioning_logger.info("no loginid: {}".format(o))
if people:
length = len(people)
sitrep = 1
notes = ''
session = get_session(EARL)
response = _generate_files(people, filetype, 'new_people')
if not response:
sitrep = 0
info_logger.info("{} file was not generated".format(filetype))
info_logger.info("people = {}".format(people))
# create batch record
rec = ProvisioningBatchRec(total=length,
sitrep=sitrep,
notes=notes)
session.add(rec)
session.commit()
else:
# create batch record
rec = ProvisioningBatchRec(total=length,
sitrep=sitrep,
notes=notes)
session.add(rec)
session.commit()
# batch record ID
rid = rec.batch_no
for p in people:
notes = ''
csv = '|'.join(
['{}'.format(value) for (key, value) in p.items()])
if test:
debug_logger.debug("csv = {}".format(csv))
try:
sql = INSERT_EMAIL_RECORD(cid=p.id, ldap=p.loginid)
if test:
debug_logger.debug(
"INSERT_EMAIL_RECORD = {}".format(sql))
else:
do_sql(sql, key=key, earl=EARL)
except:
notes += "failed insert = {}|{}|".format(p, sql)
provisioning_logger.info(
"INSERT_EMAIL_RECORD fail = {}|{}".format(p, sql))
try:
sql = INSERT_CVID_RECORD(cid=p.id, ldap=p.loginid)
if test:
debug_logger.debug(
"INSERT_CVID_RECORD = {}".format(sql))
else:
do_sql(sql, key=key, earl=EARL)
except:
notes += "failed insert = {}|{}".format(p, sql)
provisioning_logger.info(
"INSERT_CVID_RECORD fail = {}|{}".format(p, sql))
# convert datetime object to string because informix
try:
dob = p.dob.strftime("%m-%d-%Y")
except:
dob = None
# insert detail record
sql = INSERT_DETAIL_RECORD(batch_id=rid,
username=p.loginid,
last_name=p.lastname,
first_name=p.firstname,
cid=p.id,
faculty=p.facultystatus,
staff=p.staffstatus,
student=p.studentstatus,
retire=p.retirestatus,
dob=dob,
postal_code=p.zip,
account=p.accttypes,
proxid=p.proxid,
phone_ext=p.phoneext,
departments=p.depts,
csv=csv,
notes=notes)
try:
if test:
debug_logger.debug("sql = {}".format(sql))
do_sql(sql, key=key, earl=EARL)
except Exception as e:
provisioning_logger.info("insert fail: p = {}".format(p))
if test:
provisioning_logger.info("sql fail = {}".format(e))
print("die: sql fail")
exit(-1)
session.close()
else:
if test:
print("No objects found for provisioning.")
else:
info_logger.info("No objects found for provisioning.")
def form(request, stype, cid=None):
medical_staff=False
staff = in_group(request.user, settings.STAFF_GROUP)
if not cid:
cid = request.user.id
else:
if not staff:
return HttpResponseRedirect(
reverse_lazy('home')
)
else:
medical_staff=True
# get academic term
term = get_term()
# get student
sql = ''' {}
WHERE
id_rec.id = "{}"
AND stu_serv_rec.yr = "{}"
AND stu_serv_rec.sess = "{}"
'''.format(
STUDENT_VITALS, cid, term['yr'], term['sess']
)
engine = get_engine(EARL)
obj = engine.execute(sql)
student = obj.fetchone()
if not student:
if medical_staff:
return HttpResponseRedirect(
reverse_lazy('dashboard_home')
)
else:
return HttpResponseRedirect(
reverse_lazy('home')
)
# create database session
session = get_session(settings.INFORMIX_EARL)
# obtain our student medical manager
manager = get_manager(session, cid)
# obtain our health insturance object
insurance = session.query(StudentHealthInsurance).\
filter_by(college_id=cid).\
filter(StudentHealthInsurance.current(settings.START_DATE)).first()
update = None
data = row2dict(insurance)
if data:
update = cid
# opt out
oo = data.get('opt_out')
# UI display for 1st, 2nd, and 3rd forms
primary = data.get('primary_dob')
secondary = data.get('secondary_dob')
tertiary = data.get('tertiary_dob')
# form name
fname = '{}Form'.format(stype.capitalize())
# form class
form = str_to_class('djsani.insurance.forms', fname)(
initial=data, manager=manager, insurance=insurance
)
if request.method=='POST':
update = request.POST.get('update')
form = str_to_class(
'djsani.insurance.forms', fname
)(request.POST, request.FILES, manager=manager, insurance=insurance)
if form.is_valid():
form = form.cleaned_data
# update the manager
manager.cc_student_health_insurance=True
# commit, because the above has not been saving properly
# for some reason when opt-out
session.commit()
# opt out of insurance
oo = form.get('opt_out')
if oo:
if manager.athlete:
if not medical_staff:
# alert email to staff
if settings.DEBUG:
TO_LIST = [settings.SERVER_EMAIL,]
else:
TO_LIST = settings.INSURANCE_RECIPIENTS
send_mail(
request, TO_LIST,
u"[Health Insurance] Opt Out: {} {} ({})".format(
request.user.first_name,request.user.last_name,cid
), request.user.email,
'alert_email.html',
request, settings.MANAGERS
)
else:
# empty table
form = STUDENT_HEALTH_INSURANCE
else:
# deal with file uploads
if request.FILES:
folder = 'insurance/{}/{}'.format(
cid, manager.created_at.strftime('%Y%m%d%H%M%S%f')
)
p = join(settings.UPLOADS_DIR, folder)
if request.FILES.get('primary_card_front'):
front = handle_uploaded_file(
request.FILES['primary_card_front'], p
)
form['primary_card_front'] = '{}/{}'.format(
folder, front
)
else:
form.pop('primary_card_front', None)
if request.FILES.get('primary_card_back'):
back = handle_uploaded_file(
request.FILES['primary_card_back'], p
)
form['primary_card_back'] = '{}/{}'.format(
folder, back
)
else:
form.pop('primary_card_back', None)
else:
form.pop('primary_card_front', None)
form.pop('primary_card_back', None)
# student did not opt out
form['opt_out'] = False
# insert else update
if not update:
# insert
form['college_id'] = cid
form['manager_id'] = manager.id
s = StudentHealthInsurance(**form)
session.add(s)
else:
# fetch our insurance object
obj = session.query(StudentHealthInsurance).\
filter_by(college_id=cid).\
filter(StudentHealthInsurance.current(settings.START_DATE)).\
first()
# update it with form values
for key, value in form.iteritems():
setattr(obj, key, value)
# lastly, commit and redirect
session.commit()
if staff:
redirect = reverse_lazy('student_detail', args=[cid])
else:
redirect = reverse_lazy('insurance_success')
return HttpResponseRedirect(redirect)
else:
primary = data.get('primary_dob')
secondary = request.POST.get('secondary_dob')
tertiary = request.POST.get('tertiary_dob')
# close database session
session.close()
return render(
request, 'insurance/form.html', {
'form':form,'update':update,'oo':oo,'student':student,
'medical_staff':medical_staff, 'manager':manager,
'primary':primary,'secondary':secondary,'tertiary':tertiary
}
)