def inactivate(self):
#Step 1: Set inactive date for permit record
inactivateSQL = (
' UPDATE'
' prkgpermt_rec'
' SET'
' inactive_date = CURRENT,'
' permt_stat = ""'
' WHERE'
' permt_no = %s'
) % (self.permit_no)
do_sql(inactivateSQL)
#Step 2: Open up spot in parking lot table
freeUpSpotSQL = (
' UPDATE'
' prkglot_rec'
' SET'
' lot_stat = ""'
' WHERE'
' lotloctn = "%s"'
' AND'
' lot_acadyr = "%s"'
) % (self.lotloctn, self.acad_yr)
do_sql(freeUpSpotSQL)
def test_insert_detail_record(self):
print("\n")
print("insert detail record SQL incantation")
seperator()
sql = SELECT_NEW_PEOPLE(where='AND subID.id = {}'.format(self.cid))
p = do_sql(sql, key='debug', earl=EARL).first()
print(p)
print(p.dob)
# convert datetime object to string because informix
#dob = p.dob.strftime("%Y-%m-%d")
dob = p.dob.strftime("%m-%d-%Y")
sql = INSERT_DETAIL_RECORD(batch_id=40,
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='',
notes='')
do_sql(sql, key='debug', earl=EARL)
def assignPermit(id, veh_no, nextSpot, acadYear):
insertPermitSQL = (
" INSERT INTO prkgpermt_rec"
" (lotcode, lotloctn, permit_code, acadyr, permt_id, veh_no, permt_stat, active_date, permtcmmnt)"
" VALUES ('%s', '%s', '%s', '%s', %s, %s, '%s', '%s', '%s')"
) % (nextSpot.lotcode, nextSpot.lotloctn, '', acadYear, id, veh_no, '', TODAY, '')
do_sql(insertPermitSQL)
def save(self):
# convert None to ''
xstr = lambda s: '' if not s else str(s)
# put data in staging tables
insertSQL = '''
INSERT INTO
cc_stg_changemajor (
student_id, major1, major2, major3, minor1,
minor2, minor3, advisor_id, datecreated
)
VALUES (
{},"{}","{}","{}","{}","{}","{}","{}", CURRENT
)
'''.format(
self.__dict__['student_id'],
xstr(self.__dict__['major1']),
xstr(self.__dict__['major2']),
xstr(self.__dict__['major3']),
xstr(self.__dict__['minor1']),
xstr(self.__dict__['minor2']),
xstr(self.__dict__['minor3']),
xstr(self.__dict__['advisor']),
)
do_sql(
insertSQL,
key=settings.INFORMIX_DEBUG,
earl=settings.INFORMIX_EARL
)
def insert_address(
aa_type, cid, address_line1, address_line2,
address_line3, city, state, postalcode, country, phone
):
clear_sql = '''
UPDATE
stg_aludir_address
SET
approved = "N"
WHERE
id = {}
AND
aa = "{}"
AND
NVL(approved,"") = ""
'''.format(cid, aa_type)
do_sql(clear_sql, INFORMIX_DEBUG)
address_sql = '''
INSERT INTO stg_aludir_address (
aa, id, address_line1, address_line2, address_line3, city,
state, zip, country, phone, submitted_on
)
VALUES (
"{}", {}, "{}", "{}", "{}", "{}",
"{}", "{}", "{}", "{}", TO_DATE("{}", "%Y-%m-%d")
)
'''.format(
aa_type, cid, address_line1, address_line2, address_line3,
city, state, postalcode, country, phone, NOW
)
do_sql(address_sql, INFORMIX_DEBUG)
return address_sql
def reserveSpot(acadYear, lot):
getNextSQL = (
" SELECT"
" lotcode, MIN(lot_no) AS lot_no, MIN(lotloctn) AS lotloctn"
" FROM"
" prkglot_rec"
" WHERE"
" TODAY BETWEEN active_date AND NVL(inactive_date, TODAY)"
" AND"
" TRIM(lot_stat) = ''"
" AND"
" lot_acadyr = '%s'"
" AND"
" lotcode = '%s'"
" GROUP BY lotcode"
) % (acadYear, lot)
getNext = do_sql(getNextSQL).fetchone()
reserveSpotSQL = (
" UPDATE prkglot_rec"
" SET lot_stat = 'A'"
" WHERE"
" lot_no = %s"
) % (getNext.lot_no)
do_sql(reserveSpotSQL)
return getNext.lot_no
def save(self):
# put data into staging tables
sql = u'''
INSERT INTO cc_stg_undergrad_candidacy
(
student_id, first_name, middle_initial, last_name,
first_name_pronounce, middle_name_pronounce,
last_name_pronounce, major1, major2, major3, minor1,
minor2, minor3, plan_to_walk, grad_yr, grad_sess, prog,
aa, aa_value, address, city, state,
zip, diploma_aa_type, datecreated
)
VALUES
(
{student_id},"{fname}","{mname}","{lname}","{fnamepro}",
"{mnamepro}","{lnamepro}","{major1}","{major2}","{major3}",
"{minor1}","{minor2}","{minor3}",
"{participate_in_graduation}","{grad_yr}","{grad_session}",
"{will_teach}","{best_contact}","{best_contact_value}",
"{address}","{city}","{state}","{zipcode}",
"{diploma_aa_type}", CURRENT
)
'''.format(**self.__dict__)
insertSQL = sql.replace('"True"','"t"').replace('"False"','"f"')
do_sql(insertSQL, key=DEBUG, earl=EARL)
def getstudentexams(request):
q = request.POST.get("student", None)
def isInt(value):
try:
return int(value)
except:
return 0
# hold onto the student's id (if a number was entered), or zero (if a name was entered)
studentID = isInt(q)
sqlExamsForStudent = ('''SELECT exam_rec.id, trim(id_rec.firstname) as first, trim(id_rec.lastname) as last,
exam_rec.ctgry, trim(exam_table.txt) as exam, exam_rec.cmpl_date, trim(exam_rec.remark) as remark,
trim(exam_table.lbl1) as label, adm_rec.plan_enr_yr, adm_rec.plan_enr_sess
FROM id_rec
left join (exam_rec
left join exam_table
on exam_rec.ctgry = exam_table.exam
AND exam_table.lbl1= "PLACEMENT"
)
on id_rec.id = exam_rec.id
left join adm_rec on id_rec.id = adm_rec.id
WHERE id_rec.id = \'%s\' '''
% (studentID))
exams = do_sql(sqlExamsForStudent)
sqlAllExams = 'SELECT exam, txt FROM exam_table WHERE lbl1 = \'PLACEMENT\' order by exam'
allexams = do_sql(sqlAllExams)
context = {'exams':exams, 'allexams':allexams, 'panel':'searchByStudent', }
t = 'students/studentexams.html'
return render_to_response(t, context, RequestContext(request))
def expireVehicle(veh_no):
vehicleExpireSQL = (
" UPDATE veh_rec"
" SET inactive_date = TODAY"
" WHERE veh_no = %s"
) % (veh_no)
do_sql(vehicleExpireSQL)
return veh_no
def permitUpdate(permit_no, active_date, inactive_date, comment):
updatePermitSQL = (
" UPDATE prkgpermt_rec"
" SET active_date = '%s',"
" inactive_date = '%s',"
" permtcmmnt = '%s'"
" WHERE permt_no = %s"
) % (active_date, inactive_date, comment, permit_no)
do_sql(updatePermitSQL)
return updatePermitSQL
def addVehicle(id, license, st_plate, make, model, model_yr, acadyr):
insertVehicleSQL = (
" INSERT INTO veh_rec (id, license, st_plate, make, model, model_yr, acadyr, issued_date)"
" VALUES (%s, '%s', '%s', '%s', '%s', %s, '%s', TODAY)"
) % (id, license, st_plate, make, model, model_yr, acadyr)
do_sql(insertVehicleSQL)
#getVehicleSQL = ("SELECT DISTINCT dbinfo('sqlca.sqlerrd1') AS veh_no FROM veh_rec")
getVehicleSQL = ("SELECT veh_no FROM veh_rec WHERE id = %s AND license = '%s' AND acadyr = '%s'") % (id, license, acadyr)
veh_results = do_sql(getVehicleSQL).fetchone()
return veh_results.veh_no
def save(self):
insertSQL = '''INSERT INTO cc_stg_ferpadirectory (student_id, consent, datecreated)
VALUES (%(student_ID)s, "%(consent)s", CURRENT)''' % (self.__dict__)
do_sql(insertSQL, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
updateSQL = '''UPDATE profile_rec
SET priv_code = '''
if self.__dict__['consent'] == "NOCONSENT":
updateSQL += '"FERP"'
else:
updateSQL += '""'
updateSQL += 'WHERE id = %s' % (self.__dict__['student_ID'])
do_sql(updateSQL, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
def insert_relative(cid, relCode, fname, lname, alumPrimary):
sql = '''
INSERT INTO stg_aludir_relative (
id, relCode, fname, lname, alum_primary, submitted_on
)
VALUES
({}, '{}', '{}', '{}', '{}', TO_DATE('{}', '%Y-%m-%d'))
'''.format(
cid, relCode, fname, lname, alumPrimary, NOW
)
do_sql(sql, INFORMIX_DEBUG)
return sql
def clear_relative(cid):
sql = '''
UPDATE
stg_aludir_relative
SET
approved = "N"
WHERE
id = {}
AND
NVL(approved,"") = ""
'''.format(cid)
do_sql(sql, INFORMIX_DEBUG)
def insert_privacy(cid, field, display):
privacy_sql = '''
INSERT INTO stg_aludir_privacy (
id, fieldname, display, lastupdated
)
VALUES (
{}, "{}", "{}", TO_DATE("{}", "%Y-%m-%d")
)
'''.format(
cid, field, display, NOW
)
do_sql(privacy_sql, INFORMIX_DEBUG)
return privacy_sql
def insert_activity(cid, activityText):
activity_sql = '''
INSERT INTO stg_aludir_activity (
id, activityText, submitted_on
)
VALUES (
{}, "{}", TO_DATE("{}", "%Y-%m-%d")
)
'''.format(
cid, activityText.strip(), NOW
)
do_sql(activity_sql)
return activity_sql
def clear_activity(cid):
clear_sql = '''
UPDATE
stg_aludir_activity
SET
approved = "N"
WHERE
id = {}
AND
NVL(approved,"") = ""
'''.format(cid)
do_sql(clear_sql, INFORMIX_DEBUG)
def updateVehicle(veh_no, license, st_plate, make, model, model_yr):
updateVehicleSQL = (
" UPDATE veh_rec"
" SET license = '%s',"
" st_plate = '%s',"
" make = '%s',"
" model = '%s',"
" model_yr = %s"
" WHERE"
" veh_no = %s"
) % (license, st_plate, make, model, model_yr, veh_no)
do_sql(updateVehicleSQL)
return veh_no
def admin(request):
"""
main admin page
"""
# if the delete button was clicked. remove entry from database
if request.POST:
deleteMajorSQL = '''
DELETE FROM
cc_stg_changemajor
WHERE
changemajor_no = {}'''.format(request.POST['record'])
do_sql(
deleteMajorSQL, key=DEBUG, earl=EARL
)
# get all entries in database along with advisor full name
# and major/minor full text
getListSQL = '''
SELECT
cm.*, TRIM(id_rec.firstname) AS firstname,
TRIM(id_rec.lastname) AS lastname,
TRIM(advisor.firstname) AS advisor_first,
TRIM(advisor.lastname) AS advisor_last,
TRIM(majors1.txt) AS major1_txt,
TRIM(majors2.txt) AS major2_txt,
TRIM(majors3.txt) AS major3_txt,
TRIM(minors1.txt) AS minor1_txt,
TRIM(minors2.txt) AS minor2_txt,
TRIM(minors3.txt) AS minor3_txt
FROM
cc_stg_changemajor cm
INNER JOIN id_rec ON cm.student_id = id_rec.id
LEFT JOIN id_rec advisor ON advisor.id = cm.advisor_id
LEFT JOIN major_table majors1 ON cm.major1 = majors1.major
LEFT JOIN major_table majors2 ON cm.major2 = majors2.major
LEFT JOIN major_table majors3 ON cm.major3 = majors3.major
LEFT JOIN minor_table minors1 ON cm.minor1 = minors1.minor
LEFT JOIN minor_table minors2 ON cm.minor2 = minors2.minor
LEFT JOIN minor_table minors3 ON cm.minor3 = minors3.minor
ORDER BY
cm.approved, cm.datecreated DESC
'''
student = do_sql(
getListSQL, key=DEBUG, earl=EARL
)
all_students = get_all_students()
return render(request, 'changemajor/home.html', {
'student':student,
'full_student_list':all_students
})
def admin(request):
if request.POST:
sql2 = '''DELETE FROM cc_stg_ferpadirectory
WHERE ferpadirectory_no = %s''' % (request.POST['record'])
do_sql(sql2, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
sql = '''SELECT fd.*, id_rec.firstname, id_rec.lastname
FROM cc_stg_ferpadirectory AS fd
INNER JOIN id_rec
ON fd.student_id = id_rec.id
ORDER BY fd.datecreated DESC'''
student = do_sql(sql, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
return render(request, 'consentform/home.html', {
'student': student,
'full_student_list': get_all_students(),
})
def create(self, lotcode, lotloctn, permit_code, acadyr, student_id, veh_no, comment):
insertPermitSQL = (
' INSERT INTO prkgpermt_rec (lotcode, lotloctn, permit_code, acad_yr, permt_id, veh_no, permt_stat, active_date, permtcmmnt)'
' VALUES ("%s", "%s", "%s", "%s", %s, %s, "A", CURRENT, "%s")'
) % (lotcode, lotloctn, permit_code, acadyr, student_id, veh_no, comment)
do_sql(insertPermitSQL)
getpermitidSQL = (
' SELECT permt_no'
' FROM prkgpermt_rec'
' WHERE veh_no = %s'
' AND inactive_date IS NULL'
) % (veh_no)
permit = do_sql(getpermitidSQL).fetchone()
self = Permit(permit.permt_no)
def __init__(self, *args, **kwargs):
user = kwargs.pop('user')
super(ProposalApproverForm, self).__init__(*args, **kwargs)
# populate the approvers select field with faculty/staff
facstaff = do_sql(FACSTAFF_ALPHA)
approvers = [('','-----------')]
cid = None
for r in facstaff:
if cid != r.id:
name = u'{}, {}'.format(r.lastname, r.firstname)
approvers.append((r.id, name))
cid = r.id
self.fields['user'].choices = approvers
# populate the proposals select field
proposals = get_proposals(user)
if proposals['objects']:
props = [('','-----------')]
for p in proposals['objects']:
title = u'{}: by {}, {}'.format(
p.title, p.user.last_name, p.user.first_name
)
props.append((p.id,title))
self.fields['proposal'].choices = props
else:
self.fields['proposal'].widget.attrs['class'] = 'error'
def get_email(cx_id):
'''
accepts: user ID
returns: user's email
'''
email_sql = '''
SELECT
TRIM(aa_rec.line1) AS email
FROM
aa_rec
WHERE
id = {}
AND
aa = "EML1"
AND
TODAY BETWEEN beg_date AND NVL(end_date, TODAY)
'''.format(cx_id)
obj = do_sql(
email_sql, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL
)
try:
email = obj.first()['email']
except:
email = None
return email
def populateForm(student_id):
form = DataReqForm()
#get student's id, name, and majors/minors
getStudentSQL = '''
SELECT
IDrec.id, TRIM(IDrec.firstname) AS firstname, TRIM(IDrec.middlename) AS middlename, TRIM(IDrec.lastname) AS lastname,
TRIM(major1.major) AS major1code, TRIM(major2.major) AS major2code, TRIM(major3.major) AS major3code,
TRIM(minor1.minor) AS minor1code, TRIM(minor2.minor) AS minor2code, TRIM(minor3.minor) AS minor3code
FROM
id_rec IDrec INNER JOIN prog_enr_rec PROGrec ON IDrec.id = PROGrec.id
LEFT JOIN major_table major1 ON PROGrec.major1 = major1.major
LEFT JOIN major_table major2 ON PROGrec.major2 = major2.major
LEFT JOIN major_table major3 ON PROGrec.major3 = major3.major
LEFT JOIN minor_table minor1 ON PROGrec.minor1 = minor1.minor
LEFT JOIN minor_table minor2 ON PROGrec.minor2 = minor2.minor
LEFT JOIN minor_table minor3 ON PROGrec.minor3 = minor3.minor
WHERE
IDrec.id = %d''' % (student_id)
student = do_sql(getStudentSQL, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
for row in student: #set student's initial data
form.fields['student_id'].initial = row['id']
form.fields['fname'].initial = row['firstname']
form.fields['mname'].initial = row['middlename']
form.fields['lname'].initial = row['lastname']
form.fields['major1'].initial = row['major1code']
form.fields['major2'].initial = row['major2code']
form.fields['major3'].initial = row['major3code']
form.fields['minor1'].initial = row['minor1code']
form.fields['minor2'].initial = row['minor2code']
form.fields['minor3'].initial = row['minor3code']
return form
def index(request):
if request.POST: #If we do a POST
form = SearchForm(
request.POST
) #Scrape the data from the form and save it in a variable
if form.is_valid(): #If the form is valid
sql = """SELECT veh_rec.*, id_rec.firstname, id_rec.lastname
FROM veh_rec
LEFT JOIN id_rec
ON veh_rec.id = id_rec.id
WHERE veh_rec.id = CAST('%(isearch)s' AS int)
OR veh_rec.license = '%(lsearch)s'
OR veh_rec.decal = '%(psearch)s'""" % (form.cleaned_data)
results = do_sql(sql,
key=settings.INFORMIX_DEBUG,
earl=settings.INFORMIX_EARL)
return render(request, 'permitcheck/results.html', {
'result': results.first(),
})
else:
form = SearchForm()
return render(request, 'permitcheck/home.html', {
'form': form,
})
def search_activity(request):
search_string = request.GET.get('term','Soccer')
objs = do_sql(
ACTIVITY_SEARCH(search_string = search_string.lower()), INFORMIX_DEBUG
)
return HttpResponse(objs.fetchall())
def schools(request):
"""
Accepts: POST request with variables 'city' and 'state'
Returns: all schools in that city/state.
"""
if request.method == "POST":
city = request.POST.get('city')
state = request.POST.get('state')
sql = '''
SELECT
fullname as school, ceeb as code
FROM
id_rec
INNER JOIN
sch_rec ON id_rec.id = sch_rec.id
WHERE
(sch_rec.ctgry = "COL" OR sch_rec.ctgry = "CC")
AND st = "%s"
AND city = "%s"
ORDER BY fullname
''' % (state, city)
schools = do_sql(sql)
html = ""
for school in schools:
if school[0]:
html += '<option value="%s">%s</option>' % (school[1],
school[0])
return HttpResponse(html, content_type="text/plain; charset=utf-8")
else:
return HttpResponse("POST required",
content_type="text/plain; charset=utf-8")
def updateStatus(self, new_status):
inactive_date = '"NULL"'
if new_status != '' and new_status != 'A':
inactive_date = "CURRENT"
updateStickerSQL = (
' UPDATE'
' prkgstckr_rec'
' SET'
' permt_stat = "%s",'
' inactive_date = "%s"'
' WHERE'
' permit_stckrcd = "%s"'
' AND'
' permit_acadyr = "%s"'
) % (new_status, inactive_date, self.code, self.acadyr)
do_sql(updateStickerSQL)
def school_cities(request):
"""
Accepts: POST request with variable 'state'
Returns: all those cities with schools.
"""
if request.method == "POST":
state = request.POST.get('state')
sql = '''
SELECT
city
FROM
id_rec
INNER JOIN
sch_rec ON id_rec.id = sch_rec.id
WHERE
(sch_rec.ctgry = "COL" OR sch_rec.ctgry = "CC")
AND
st = "%s"
GROUP BY city
ORDER BY city
''' % state
cities = do_sql(sql)
html = ""
for city in cities:
if city[0]:
html += '<option value="%s">%s</option>' % (city[0], city[0])
return HttpResponse(html, content_type="text/plain; charset=utf-8")
else:
return HttpResponse("POST required",
content_type="text/plain; charset=utf-8")
def test_activities_invalid(self):
print("\n")
print("test activities select statement with invalid college ID")
print(seperator())
# organization, club, etc.
is_sports = False
fieldname = 'activity' if not is_sports else 'sport'
comparison = 'NOT' if not is_sports else ''
sql = ACTIVITIES(
cid = self.cid_null, fieldname = fieldname,
comparison = comparison
)
if settings.DEBUG:
print(sql)
else:
print("use the --debug-mode flag to print Activities SQL")
activities = do_sql(sql, self.debug, self.earl).fetchall()
print(activities)
self.assertEqual(len(activities), 0)
def get_message_info(cid):
message_sql = '''
SELECT
ids.id,
NVL(
TRIM(email.line1) || TRIM(email.line2) || TRIM(email.line3), ""
) AS email,
TRIM(ids.firstname) AS firstname,
TRIM(ids.lastname) AS lastname
FROM
id_rec ids
LEFT JOIN
aa_rec email
ON
ids.id = email.id
AND
email.aa = "EML2"
AND
TODAY BETWEEN email.beg_date
AND
NVL(email.end_date, TODAY)
WHERE
ids.id = {}
'''.format(cid)
message = do_sql(message_sql, INFORMIX_DEBUG)
return message.fetchone()
def contact(request):
'''
retrieves student's contact info from form through ajax call
'''
getContactSQL = '''
SELECT
*
FROM
aa_rec
WHERE
id = {id}
AND
aa = "{aa}"
AND
TODAY BETWEEN beg_date AND NVL(end_date, TODAY)
'''.format(
request.GET['id'], request.GET['aa']
)
contactinfo = do_sql(getContactSQL, key=DEBUG, earl=EARL)
data = ''
for row in contactinfo:
# we order the data this way so we can put it into a dict
# when it's recieved. HttpResponse only sends strings
for key in contactinfo.keys():
data = data + key + ':' + str(row[key]) + ','
return HttpResponse(data)
def search_ldap(request):
"""
Search the LDAP store for an alumna's record.
POST required, which is sent via Ajax request.
If we find a record, we check Informix to see
if we have their LDAP username stored, and
update it if not. Lastly, display login form.
If no record, allow the user to create one.
"""
if request.method == 'POST':
form = RegistrationSearchForm(request.POST)
if form.is_valid():
# data dictionary
data = form.cleaned_data
# search ldap
# we use the regular ldap server here
l = LDAPManager()
user = l.search(data['alumna'])
if user:
# we have a user
user = user[0][1]
# update informix if no ldap_user
if not settings.DEBUG and data['ldap_name'] == '':
sql = '''
UPDATE cvid_rec SET ldap_name='{}',
ldap_add_date = TODAY
WHERE cx_id = '{}'
'''.format(user['cn'][0], data['alumna'])
results = do_sql(sql, key=settings.INFORMIX_DEBUG)
# check for challenge questions
l = LDAPBackend()
request.session['ldap_questions'] = l.get_questions(
user['cn'][0]
)
# display the login form
form = {'data':{'username':user['cn'][0],}}
redir = reverse_lazy('alumni_directory_home')
extra_context = {
'user':user,'form':form,
'next':redir,'action':settings.LOGIN_URL
}
template = 'login'
else:
# display the create form
data['carthageNameID'] = data['alumna']
request.session['ldap_name'] = data.get('ldap_name')
form = CreateLdapForm(initial=data)
action = reverse_lazy('registration_create_ldap')
extra_context = {'action':action,'form':form,}
template = 'create'
return render(
request,
'registration/{}_ldap.inc.html'.format(template),
extra_context
)
else:
# POST required
# or doing something nefarious
return HttpResponseRedirect(reverse_lazy('registration_search'))
def get_all_students(): #get all entries in table for use by jquery autocomplete
sql = '''
SELECT
first_name, last_name, middle_initial, student_id
FROM
cc_stg_undergrad_candidacy
'''
return do_sql(sql, key=settings.INFORMIX_DEBUG, earl=settings.INFORMIX_EARL)
def main():
"""main function."""
sql = """
SELECT
lastname, firstname, username
FROM
provisioning_vw
WHERE
{} is not null
ORDER BY
lastname, firstname
""".format(who)
if test:
print("sql = {}".format(sql))
else:
user_list = do_sql(sql, key=settings.INFORMIX_DEBUG, earl=EARL)
for user in user_list:
email = '{0}@carthage.edu'.format(user.username)
try:
credentials = get_cred(email, 'calendar')
http = httplib2.Http()
http = credentials.authorize(http)
service = build('calendar', 'v3', http=http)
except Exception as error:
print('{0}|{1}|{2}|Invalid email'.format(user.lastname, user.firstname, email))
continue
page_token = None
domain = None
while True:
try:
acl = service.acl().list(calendarId=email).execute()
except Exception:
print('{0}|{1}|{2}|No calendar'.format(user.lastname, user.firstname, email))
break
for rule in acl['items']:
#print(rule)
#print('%s: %s' % (rule['id'], rule['role']))
# type == 'domain' value == 'carthage.edu'
#print(rule['scope']['type'])
#print(rule['scope']['value'])
if rule['scope']['type'] == 'domain':
domain = rule['role']
break
page_token = acl.get('nextPageToken')
if not page_token:
break
# sometimes the google returns a shit tonne of pages that are just repeats
if domain:
break
if domain:
print('{0}|{1}|{2}|{3}'.format(user.lastname, user.firstname, email, domain))
else:
print('{0}|{1}|{2}|No access'.format(user.lastname, user.firstname, email))
def test_select_new_people(self):
print("\n")
print("select new people SQL incantations")
seperator()
sql = SELECT_NEW_PEOPLE(where='')
objects = do_sql(sql, key='debug', earl=EARL)
for o in objects:
print("{}|{}|{}|{}|{}".format(o.loginid, o.lastname, o.firstname,
o.id, o.dob))
def psearch(request):
sql = """SELECT decal
FROM veh_rec
WHERE decal LIKE '%%%s%%'""" % (request.POST['permit'])
results = do_sql(sql)
results_string = ''
for thing in results:
temp = thing['decal']
if temp:
if results_string:
results_string = results_string + ','
results_string = results_string + temp
return HttpResponse(results_string)
def lsearch(request):
sql = """SELECT license
FROM veh_rec
WHERE license LIKE '%%%s%%'""" % (request.POST['license'])
results = do_sql(sql)
results_string = ''
for thing in results:
temp = thing['license']
if temp:
if results_string:
results_string = results_string + ','
results_string = results_string + temp
return HttpResponse(results_string)
def isearch(request):
sql = """SELECT id
FROM veh_rec
WHERE CAST(id AS varchar(32)) LIKE '%%%s%%'""" % (
request.POST['id'])
results = do_sql(sql)
results_string = ''
for thing in results:
temp = thing['id']
if temp:
if results_string:
results_string = results_string + ','
results_string = results_string + str(temp)
return HttpResponse(results_string)
def fn_validate_supervisor(id, EARL):
try:
if id < 1 or id is None or id == "":
return 0
else:
# Since hrstat is not going to be valid, all I can do
# is make sure the supervisor exists
# But I have to use the ADP ID, not the CX ID
q_val_super = '''select adp_id from cvid_rec
where adp_id = {0}
'''.format(id)
# print(q_val_super)
# print("ID = " + str(id))
sql_val_super = do_sql(q_val_super, key=DEBUG, earl=EARL)
row = sql_val_super.fetchone()
if row == None:
# Not found
return(0)
else:
return (id)
# Not Eligible
# if row[0] == 'No':
# return(0)
# elif row[0].strip() == 'OTH':
# return(0)
# elif row[0].strip() == 'LV':
# return(0)
# elif row[0].strip() == 'SA':
# return(0)
# elif row[0].strip() == 'STU':
# return(0)
# elif row[0].strip() == 'PDG':
# return(0)
# elif row[0].strip() == 'STD':
# return(0)
# Valid as Supervisor
# else:
except ValueError as e:
fn_write_log("Value error in jobrec.py fn_validate_supervisor. ID = "
+ id + ", " + last + ", " + first + " Err = " + e.message)
except Exception as e:
# print(e)
fn_write_error("Error in jobrec.py fn_validate_supervisor. ID = "
+ id + ", " + last + ", "
+ first + " Err = " + e.message)
return(0)
def main():
# execute SQL statement
sqlresult = do_sql(GET_GL_ACCTS, earl=EARL)
# formatting date and time string
datetimestr = time.strftime("%Y-%m-%d")
# set directory and filename
filename = ('{0}gl_data/glrec_data.csv'.format(
settings.PAPERCUT_CSV_OUTPUT))
# set destination path and new filename that it will be renamed
# to when archived
archive_destination = ('{0}/gl_data/{1}_{2}.csv'.format(
settings.PAPERCUT_CSV_ARCHIVED, 'glrec_data_bak', datetimestr))
# opens a file for writing
with open(filename, 'w') as glrecfile:
for row in sqlresult:
try:
# creates a formatted string
accountName = ("{0}/{1}-{2}-{3}".format(
row['acct_descr'].split('/', 1)[1], row['fund'],
row['func'], row['obj']))
# writes a formatted string to the file
glrecfile.write('{0}\n'.format(accountName))
except Exception as e:
#print "Exception: {0}".format(str(e))
# Email that there was an exception error while processing .csv
SUBJECT = "[Papercut] GL Exception Error"
BODY = "There was an exception error: {0}".format(str(e))
send_mail(None, TO, SUBJECT, FROM, TEMPLATE, BODY, bcc=BCC)
# close file
glrecfile.close()
# removed for now S. Smolik 1/19/2018
# sends email attachment
# file_attach = '{0}glrec_data.csv'.format(settings.PAPERCUT_CSV_OUTPUT)
# subject = "[GL Account Names] attachment"
# send_mail(
# None, TO, SUBJECT, FROM, TEMPLATE, BODY, bcc=BCC, attach=file_attach
#)
# renaming old filename to newfilename and move to archive location
shutil.copy(filename, archive_destination)
def main():
"""
Cycle through the users, obtain the user's calendar list, check
for the calendar
"""
sql = '{} ORDER BY id_rec.lastname, id_rec.firstname'.format(FACULTY_ALPHA)
user_list = do_sql(sql, key=settings.INFORMIX_DEBUG, earl=EARL)
cal_dict = {'id': cid, "hidden": "False"}
fails = []
exists = []
inserts = 1
credentials = get_cred(email, "calendar")
http = httplib2.Http()
credentials.authorize(http)
for user in user_list:
email = user["email"]
print email
try:
service = build("calendar", "v3", http=http)
try:
c = service.calendarList().get(calendarId=cid).execute()
print "calendar already exists."
#service.calendarList().delete(calendarId=cid).execute()
exists.append(email)
except:
print "insert calendar"
if not test:
service.calendarList().insert(body=cal_dict).execute()
inserts += 1
except:
print "{} is not a valid email".format(email)
fails.append(email)
print "Total number of users: {}".format(len(user_list))
print "Inserts = {}".format(inserts)
print "Already exists ({}) = {}".format(len(exists), exists)
print "Failures = {}".format(fails)
def fn_process_idrec(carth_id, file_number, fullname, lastname, firstname,
middlename, title, addr_line1, addr_line2, addr_line3,
city, st, zip, ctry, ctry_cod, ss_no, phone, decsd,
eff_date, EARL):
print("Start ID Rec Processing")
engine = get_engine(EARL)
v_id = fn_validate_field(carth_id, "id", "id", "id_rec", "integer", EARL)
if v_id == 0:
fn_write_log("ID not found in CX database. ID = " + carth_id +
" Name = " + fullname)
BODY = "ID not found in CX database for ID " + carth_id \
+ " Name, " + fullname
SUBJECT = "CX ID not found"
# sendmail(
# settings.ADP_TO_EMAIL, settings.ADP_FROM_EMAIL,
# BODY, SUBJECT)
else:
try:
q_update_id_rec = (
'''UPDATE id_rec SET fullname = ?, lastname = ?,
firstname = ?, middlename = ?, ss_no = ?, decsd = 'N',
upd_date = ?, ofc_add_by = 'HR'
WHERE id = ?''')
q_update_id_args = (fullname, lastname, firstname, middlename,
ss_no, eff_date, carth_id)
# print(q_update_id_rec)
# print(q_update_id_args)
fn_write_log("Update basic info in id_rec table for " + fullname +
", ID = " + str(carth_id))
scr.write(q_update_id_rec + '\n' + str(q_update_id_args) + '\n')
# logger.info("Update id_rec table");
engine.execute(q_update_id_rec, q_update_id_args)
except Exception as err:
# print(err.message)
return (err.message)
fn_write_error(
"Error in id_rec.py updating basic info. Error = " +
err.message)
# logger.error(err, exc_info=True)
#########################################################
# Title is a problem - most blank in ADP
# To avoid overwriting, will need to validate and do
# a separate update of the record
try:
if title is not None:
x = title.replace(".", "")
vTitle = fn_validate_field(x.upper(), 'title', 'title',
'title_table', 'char', EARL)
# print("Title = " + str(vTitle))
if vTitle is not None and vTitle != "":
q_update_title = ('''UPDATE id_rec SET title = ?
WHERE id = ?''')
q_update_title_args = (vTitle, carth_id)
fn_write_log("Update Title info in id_rec table for " +
fullname + ", ID = " + str(carth_id))
scr.write(q_update_title + '\n' +
str(q_update_title_args) + '\n')
# logger.info("Update id_rec table");
engine.execute(q_update_title, q_update_title_args)
# else:
# print("No Title")
except Exception as err:
# print(err.message)
return (err.message)
fn_write_error(
"Error in id_rec.py updating title info. Error = " +
err.message)
# logger.error(err, exc_info=True)
#########################################################
# print("Country Code = " + str(len(ctry_cod)))
try:
# also need to deal with address changes
# Search for existing address record
if ctry_cod.strip() != '' and len(ctry_cod) > 0:
cntry = fn_validate_field(ctry_cod, 'ctry', 'ctry',
'ctry_table', 'char', EARL)
# print("Valid Country Code = " + cntry)
# print(" In Check Address")
q_check_addr = '''
SELECT id, addr_line1, addr_line2, addr_line3, city,
st, zip, ctry, phone
FROM id_rec
WHERE id = {0}
'''.format(carth_id)
addr_result = do_sql(q_check_addr, key=DEBUG, earl=EARL)
# scr.write(q_check_addr + '\n');
row = addr_result.fetchone()
if row is None:
fn_write_log("Data missing in idrec.py address function. \
Employee not in id rec for id "
"number " + str(carth_id))
# print("Employee not in id rec")
elif str(row[0]) == '0' or str(
row[0]
) == '': # No person in id rec? Should never happen
fn_write_log("Data missing in idrec.py address function. \
Employee not in id rec for id number " +
str(carth_id))
# print("Employee not in id rec")
BODY = "ID not found in CX database id_rec.py address " \
"routine for ID " + carth_id + " Name, " + fullname
SUBJECT = "CX ID not found"
# sendmail(
# settings.ADP_TO_EMAIL, settings.ADP_FROM_EMAIL,
# BODY, SUBJECT )
# Update ID Rec and archive aa rec
elif (row[1] != addr_line1 or row[2] != addr_line2
or row[3] != addr_line3 or row[4] != city or row[5] != st
or row[6] != zip or row[7] != ctry_cod):
# print("Update: no address match in ID_REC " + str(carth_id)) #
q_update_id_rec_addr = (
'''UPDATE id_rec SET addr_line1 = ?,
addr_line2 = ?, addr_line3 = ?, city = ?, st = ?, zip = ?,
ctry = ?, aa = 'PERM', phone = ? WHERE id = ?''')
q_update_id_addr_args = (addr_line1, addr_line2,
addr_line3, city, st, zip, cntry,
fn_format_phone(phone), carth_id)
# print(q_update_id_rec_addr)
# print(q_update_id_addr_args)
fn_write_log("Update address info in id_rec table for " +
fullname + ", ID = " + str(carth_id) +
" address = " + addr_line1)
engine.execute(q_update_id_rec_addr, q_update_id_addr_args)
scr.write(q_update_id_rec_addr + '\n' +
str(q_update_id_addr_args) + '\n')
#########################################################
# Routine to deal with aa_rec
#########################################################
# now check to see if address is a duplicate in aa_rec
# find max start date to determine what date to insert
# insert or update as needed
if row[1] is None:
# This should only happen on initial run, just need to
# ignore the archive process if no address to archive
fn_write_log("Empty Address 1 in ID Rec - Nothing to "
"archive")
elif row is not None:
# print("row[1] = " + row[1])
fn_archive_address(carth_id, fullname, row[1], row[2],
row[3], row[4], row[5], row[6],
row[7], phone, EARL)
else:
fn_write_log("Empty Address 1 in ID Rec - Nothing to "
"archive")
# else:
# print("No Change " + row[1])
elif ctry_cod is None or len(ctry_cod) == 0:
# print("invalid country code" + ctry_cod)
fn_write_log("invalid country code" + ctry_cod)
except Exception as err:
# print(err.message)
fn_write_error("Error in idrec.py for id " + carth_id +
". Error = " + err.message)
def fn_set_cell_phone(phone, id, fullname, EARL):
try:
# Always get max date, in case insert has to be on same day
q_check_begin = '''
SELECT MAX(aa_rec.beg_date)
FROM aa_rec
WHERE aa_rec.id = {0} AND aa_rec.aa = 'CELL'
'''.format(id)
# print(q_check_begin)
sql_end = do_sql(q_check_begin, key=DEBUG, earl=EARL)
beg_rslt = sql_end.fetchone()
if beg_rslt[0] is None:
# print('No existing begin date')
begindate = datetime.now().strftime("%m/%d/%Y")
enddate = datetime.now().strftime("%m/%d/%Y")
# x = datetime.strptime(enddate, "%m/%d/%Y") + timedelta(days=1)
# print("Begin Date = " + str(begindate))
# print("End Date = " + str(enddate))
# elif datetime.strftime(beg_rslt[0], "%m/%d/%Y") >= datetime.strftime(datetime.now(), "%m/%d/%Y"):
else:
x = beg_rslt[0]
y = beg_rslt[0] + timedelta(days=1)
enddate = x.strftime("%m/%d/%Y")
begindate = y.strftime("%m/%d/%Y")
# print("Begin Date = " + str(begindate))
# print("End Date = " + str(enddate))
q_check_cell = '''
SELECT aa_rec.aa, aa_rec.id, aa_rec.phone, aa_rec.aa_no,
aa_rec.beg_date, aa_rec.end_date
FROM aa_rec
WHERE aa_rec.id = {0} AND aa_rec.aa = 'CELL'
AND end_date is null
'''.format(id)
# print(q_check_cell)
# print("Phone input var = " + phone)
sql_cell = do_sql(q_check_cell, key=DEBUG, earl=EARL)
cell_result = sql_cell.fetchone()
if cell_result is None:
# print("No Cell")
fn_insert_aa(id, fullname, 'CELL', "", "", "", "", "", "", "",
begindate, fn_format_phone(phone), EARL)
return ("New Cell Phone")
elif cell_result[2] == phone:
# print("Found phone = " + cell_result[2])
return ("No Cell Phone Change")
else:
# print("Found phone = " + cell_result[2])
if cell_result[5] != '':
# End date current CELL
# print("Existing cell = " + cell_result[0])
# print(datetime.strftime(end_rslt[0], "%m/%d/%Y"))
# print(datetime.strftime(datetime.now(), "%m/%d/%Y"))
fn_end_date_aa(id, cell_result[3], fullname, enddate, "CELL",
EARL)
fn_insert_aa(id, fullname, 'CELL', "", "", "", "", "", "", "",
begindate, fn_format_phone(phone), EARL)
#print("New cell will be = " + phone)
return ("Updated cell")
# else:
# print("Already end dated")
except Exception as e:
# print("Error in aarec.py, fn_set_cell_phone, for ID " + id + ", Name "
# + fullname + " Error = " + e.message)
fn_write_error("Error in aarec.py, fn_set_cell_phone, for ID " +
str(id) + ", Name " + fullname + " Error = " +
e.message)
return ""
def fn_set_email(email, id, fullname, eml, EARL):
try:
# Have to get dates regardless, because begin date part of key,
# cannot insert if date used
q_check_begin = '''
SELECT max(aa_rec.beg_date)
FROM aa_rec
WHERE aa_rec.id = {0} AND aa_rec.aa = "{1}"
'''.format(id, eml)
# print(q_check_begin)
sql_begin = do_sql(q_check_begin, key=DEBUG, earl=EARL)
beg_rslt = sql_begin.fetchone()
# print("Beg Result = " + str(beg_rslt))
# We will not update an existing email address.
# If the email matches, no change
# if no email, add new
# if existing but different, end date the old, add new
# Records of the same type cannot have the same start date.
if beg_rslt[0] is None:
begindate = datetime.now().strftime("%m/%d/%Y")
# print("Set Email New Begin Date = " + begindate)
else:
# Normally, the begin date would be today.
# If max begin date is already today, or future...
# New begin date must be 1 day greater than last one
y = beg_rslt[0] + timedelta(days=1)
# print(str(y))
# enddate = x.strftime("%m/%d/%Y")
begindate = y.strftime("%m/%d/%Y")
# print("Set Email Begin Date = " + str(begindate))
q_check_email = '''
SELECT aa_rec.aa, aa_rec.id, aa_rec.line1,
aa_rec.aa_no, aa_rec.beg_date
FROM aa_rec
WHERE aa_rec.id = {0}
AND aa_rec.aa = "{1}"
AND aa_rec.end_date IS NULL
'''.format(id, eml)
# print(q_check_email)
# logger.info("Select email info from aa_rec table");
sql_email = do_sql(q_check_email, earl=EARL)
# print("Begin Date = " + begindate)
if sql_email is not None:
email_result = sql_email.fetchone()
# print(email_result)
if email_result == None:
print("New Email will be = " + email)
# print("Begin Date = " + begindate)
fn_insert_aa(id, fullname, eml, email, "", "", "", "", "", "",
begindate, "", EARL)
return ("New email")
elif email_result[2] == email:
return ("No email Change")
# print("No Change")
else:
# End date current EMail
print("Existing Email")
# + email_result[0])
# print("Beg Date = " + str(begindate))
# print("EMAIL = " + eml + ", " + email)
enddate = datetime.now().strftime("%m/%d/%Y")
fn_end_date_aa(id, email_result[3], fullname, enddate, eml,
EARL)
fn_insert_aa(id, fullname, eml, email, "", "", "", "", "", "",
begindate, "", EARL)
return ("Email updated")
except Exception as e:
# print("Error in aarec.py, fn_set_email, for for ID " + id + ", Name "
# + fullname + " Error = " + e.message)
fn_write_error("Error in aarec.py, fn_set_email, for for ID " +
str(id) + ", Name " + fullname + ", Email " + email +
" Error = " + e.message)
return ""
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 fn_set_schl_rec(id, fullname, phone, ext, loc, room, EARL):
engine = get_engine(EARL)
q_check_schl = '''
SELECT id, aa_no, beg_date, end_date, line1, line3, phone, phone_ext
FROM aa_rec
WHERE id = {0}
AND aa = "{1}"
'''.format(id, "SCHL")
#print(q_check_schl)
try:
sql_schl = do_sql(q_check_schl, earl=EARL)
schl_result = sql_schl.fetchone()
#print("result = " + str(schl_result))
#print("Location = " + str(loc))
#print("Room = " + str(room))
location = str(loc) + " " + str(room)
if schl_result is not None:
if schl_result[4] == fullname and schl_result[5] == location \
and schl_result[6] == phone and schl_result[7] == ext:
return ("No Change in SCHL in aa_rec")
else:
q_update_schl = '''
UPDATE aa_rec
SET line1 = ?,
line3 = ?,
phone = ?,
phone_ext = ?
WHERE aa_no = ?
'''
q_upd_schl_args = (fullname, location, phone, ext,
schl_result[1])
# logger.info("update address info in aa_rec table");
engine.execute(q_update_schl, q_upd_schl_args)
fn_write_log("Update to SCHL record in aa_rec for " + fullname)
scr.write(q_update_schl + '\n' + str(q_upd_schl_args) + '\n')
#print(q_update_schl)
#print(q_upd_schl_args)
#print("update SCHL completed")
else:
# print("New SCHL rec will be added ")
# add location and room?
loc = ""
carthphone = ""
ext = ""
q_insert_schl = '''
INSERT INTO aa_rec(id, aa, beg_date, peren, end_date, line1,
line2, line3, city, st, zip, ctry, phone, phone_ext, ofc_add_by,
cell_carrier, opt_out)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
q_ins_schl_args = (id, "SCHL", datetime.now().strftime("%m/%d/%Y"),
"N", "", fullname, "", location, "", "", "", "",
phone, ext, "HR", "", "")
#print(q_insert_schl)
#print(q_ins_schl_args)
engine.execute(q_insert_schl, q_ins_schl_args)
fn_write_log("Insert SCHL into aa_rec table for " + fullname)
scr.write(q_insert_schl + '\n' + str(q_ins_schl_args) + '\n')
#print("insert SCHL completed")
except Exception as e:
# print("Error in aarec.py, fn_set_schl_rec, Error = " + e.message)
fn_write_error("Error in aarec.py, fn_set_schl_rec, for " + fullname +
", ID = " + id + "Error = " + e.message)
def fn_archive_address(id, fullname, addr1, addr2, addr3, cty, st, zp, ctry,
phone, EARL):
try:
# print(addr1, addr2, addr3)
#################################
# See if there is already an Archive record
#################################
q_check_aa_adr = '''
SELECT id, aa, aa_no, beg_date, line1, line2, line3, city, st, zip, phone,
ctry
FROM aa_rec
WHERE id = {0}
AND aa in ('PERM','PREV','SCND')
AND end_date is null
'''.format(id)
sql_id_address = do_sql(q_check_aa_adr, key=DEBUG, earl=EARL)
addr_result = sql_id_address.fetchone()
# print(q_check_aa_adr)
# print("AA_rec Addr Result = " + str(addr_result))
if addr_result is None or len(str(addr_result[2])) == 0:
# print("No archive address")
found_aa_num = 0
else:
found_aa_num = addr_result[2]
fn_write_log(
"Existing archive address record found in aa_rec for " +
fullname + ", ID = " + str(id))
# print(found_aa_num)
#################################
# Find the max start date of all PREV entries with a null end date
#################################
q_check_aa_date = '''
SELECT MAX(beg_date), ID, aa, line1, end_date
AS date_end
FROM aa_rec
Where id = {0}
AND aa = 'PREV'
AND end_date is null
GROUP BY id, aa, end_date, line1
'''.format(id)
# print(q_check_aa_date)
sql_date = do_sql(q_check_aa_date, key=DEBUG, earl=EARL)
date_result = sql_date.fetchone()
# print("AA Max date = " + str(date_result))
#################################
# Define date variables
#################################
if found_aa_num == 0 or date_result is None: #No aa rec found
max_date = datetime.now().strftime("%m/%d/%Y")
# Make sure dates don't overlap
else:
max_date = date.strftime(date_result[0], "%m/%d/%Y")
# print("Max date = " + str(max_date))
# Scenario 1
# This means that the ID_Rec address will change
# but nothing exists in aa_rec, so we will only insert as 'PREV'
if found_aa_num == 0: # No address in aa rec?
print("No existing record - Insert only")
# print(datetime.now().strftime("%m/%d/%Y"))
fn_insert_aa(id, fullname, 'PREV', addr1, addr2, addr3, cty, st,
zp, ctry,
datetime.now().strftime("%m/%d/%Y"),
fn_format_phone(phone), EARL)
# Scenario 2
# if record found in aa_rec, then we will need more options
# Find out if the record is an exact match with another address
# Question is, what is enough of a match to update rather than insert new?
elif addr_result[4] == addr1 \
and addr_result[9] == zp:
# and addr_result[7] == cty \
# and addr_result[8] == st \
# and addr_result[10] == ctry:
# or addr_result[5] == addr2 \
# or addr_result[6] == addr3 \
print("An Address exists and matches new data - Update new")
#################################
# Match found then we are UPDATING only....
#################################
fn_update_aa(id, aa, aanum, fllname, add1, add2, add3, cty, st, zp,
ctry, begdate, fn_format_phone(phone), EARL)
# to avoid overlapping dates
# Scenario 3 - AA Rec exists but does not match new address.
# End old, insert new
else:
if max_date >= str(datetime.now()):
end_date = max_date
else:
end_date = datetime.now().strftime("%m/%d/%Y")
x = datetime.strptime(end_date, "%m/%d/%Y") + timedelta(days=1)
beg_date = x.strftime("%m/%d/%Y")
# print("Check begin date = " + beg_date)
# id, aa_num, fullname, enddate, aa
fn_end_date_aa(id, found_aa_num, fullname, end_date, 'PREV', EARL)
######################################################
# Timing issue here, it tries the insert before the end date
# entry is fully committed
# Need to add something to make sure the end date is in place
# or I get a duplicate error
#########################################################
q_check_enddate = '''
SELECT aa_no, id, end_date
FROM aa_rec
WHERE aa_no = {0}
AND aa = 'PREV'
'''.format(found_aa_num)
# print(q_check_enddate)
q_confirm_enddate = do_sql(q_check_enddate, key=DEBUG, earl=EARL)
v_enddate = q_confirm_enddate.fetchone()
# print(v_enddate)
if v_enddate is not None:
fn_insert_aa(id, fullname, 'PREV', addr1, addr2,
addr3, cty, st, zp, ctry, beg_date,
fn_format_phone(phone), EARL)
else:
# print("Failure on insert. Could not verify enddate of previous")
fn_write_error("Failure on insert. Could not verify enddate "
"of previous")
# print("An Address exists but does not match - end current, insert new")
return "Success"
except Exception as e:
# print("Error in aarec.py, fn_archive_address, for ID " + id + ", Name "
# + fullname + " error = " + e.message)
fn_write_error("Error in aarec.py, fn_archive_address, for ID " +
str(id) + ", Name " + fullname + " error = " +
e.message)
def main():
############################################################################
# development server (bng), you would execute:
# ==> python schoology.py --database=train --test
# production server (psm), you would execute:
# ==> python schoology.py --database=cars
# without the --test argument
############################################################################
# set global variable
global EARL
# determines which database is being called from the command line
if database == 'cars':
EARL = INFORMIX_EARL_PROD
elif database == 'train':
EARL = INFORMIX_EARL_TEST
else:
# this will raise an error when we call get_engine()
# below but the argument parser should have taken
# care of this scenario and we will never arrive here.
EARL = None
# formatting date and time string
datetimestr = time.strftime("%Y%m%d%H%M%S")
# getting date and time string
dt = datetime.datetime.now()
# if it's after 12 PM then we will email if there any cancelled courses
if dt.hour > 12:
# check to see if there are any cancelled courses
sqlresult = do_sql(CANCELLED_COURSES, key=DEBUG, earl=EARL)
resultrow = sqlresult.fetchone()
# if the resultrow qry returns a row then we will create the courses cancelled list
if resultrow is not None:
allsqlresult = do_sql(CANCELLED_COURSES, key=DEBUG, earl=EARL)
# now get all rows for the cancelled courses
resulalltrows = allsqlresult.fetchall()
items = []
for row in resulalltrows:
items.append('COURSE: {0} - {1} {2} {3} {4}\n'.format(
row[0], row[1], row[2], row[3], row[4]))
courses_table = ''.join(items)
# send email
SUBJECT = 'SCHOOLOGY - Cancelled Courses'
BODY = 'The following courses have been cancelled.\n\n{0}'.format(
courses_table)
sendmail(settings.SCHOOLOGY_MSG_EMAIL,
settings.SCHOOLOGY_FROM_EMAIL, BODY, SUBJECT)
else:
print('Do nothing!')
else:
print('Do nothing!')
# set dictionary
sql_dict = {
'COURSES': COURSES,
'USERS': USERS,
'ENROLLMENT': ENROLLMENT,
'CROSSLIST': CROSSLIST
}
for key, value in sql_dict.items():
########################################################################
# to print the dictionary key and rows of data, you would execute:
########################################################################
if test:
print(key)
########################################################################
# Dict Value 'COURSES and SECTIONS' return all courses and sections with
# a start date less than six months from the current date.
# Based on the dates for the terms courses and sections are made active
# or inactive automatically
# Dict Value 'USERS' returns both Students and Faculty/Staff
# The student query portion pulls all students with an academic record
# between the start of the current fiscal year (July 1) and the end of
# the current fiscal year.
# The Faculty/Staff portion gets all employees with active job records
# within the last year.
# Dict Value 'ENROLLMENT' returns all students and instructors enrolled
# in a course/section with a start date less than six months from the
# current date.
# Dict Value 'CROSSLIST' returns two different sections that have the
# same meeting time and place but may have a different course number
# for a program it looks six months ahead
########################################################################
sql = do_sql(value, key=DEBUG, earl=EARL)
rows = sql.fetchall()
# set directory and filename to be stored
# ex. /data2/www/data/schoology/COURSES.csv
filename = ('{0}{1}.csv'.format(settings.SCHOOLOGY_CSV_OUTPUT, key))
# set destination path and new filename that it will be renamed to when archived
# ex. /data2/www/data/schoology_archives/COURSES_BAK_20180123082403.csv
archive_destination = ('{0}{1}_{2}_{3}.csv'.format(
settings.SCHOOLOGY_CSV_ARCHIVED, key, 'BAK', datetimestr))
# create .csv file
csvfile = open(filename, "w")
output = csv.writer(csvfile)
if rows is not None:
if key == 'COURSES': # write header row for COURSES and SECTIONS
output.writerow([
"Course Name", "Department", "Course Code", "Credits",
"Description", "Section Name", "Section School Code",
"Section Code", "Section Description", "Location",
"School", "Grading Period"
])
if key == 'USERS': # write header row for USERS
output.writerow([
"First Name", "Preferred First Name", "Middle Name",
"Last Name", "Name Prefix", "User Name", "Email",
"Unique ID", "Role", "School", "Schoology ID", "Position",
"Pwd", "Gender", "Graduation Year", "Additional Schools"
])
if key == 'ENROLLMENT': # write header row for ENROLLMENT
output.writerow([
"Course Code", "Section Code", "Section School Code",
"Unique ID", "Enrollment Type", "Grade Period"
])
if key == 'CROSSLIST': # write header row for CROSSLIST
output.writerow([
"Meeting Number", "Cross-listed Section Code",
"Target Code"
])
# creating the data rows for the .csv files
for row in rows:
if test:
print(row)
if key == 'COURSES': # write data row for COURSES
output.writerow([
row["coursename"], row["department"],
row["coursecode"], row["credits"], row["descr"],
row["sectionname"], row["secschoolcode"],
row["sectioncode"], row["secdescr"], row["location"],
row["school"], row["gradingperiod"]
])
if key == 'USERS': # write data row for USERS
output.writerow([
row["firstname"], row["preferred_first_name"],
row["middlename"], row["lastname"], row["name_prefix"],
row["username"], row["email"],
("{0:.0f}".format(0 if row['uniqueid'] is None else
row['uniqueid'])), row["role"],
row["school"], row["schoology_id"], row["position"],
row["pwd"], row["gender"], row["gradyr"],
row["additional_schools"]
])
if key == 'ENROLLMENT': # write data row for ENROLLMENT
output.writerow([
row["coursecode"], row["sectioncode"],
row["secschoolcode"],
("{0:.0f}".format(0 if row['uniqueuserid'] is None else
row['uniqueuserid'])),
row["enrollmenttype"], row["gradeperiod"]
])
if key == 'CROSSLIST': # write data row for CROSSLIST
output.writerow(
[row["mtg_no"], row["crls_code"], row["targ_code"]])
else:
SUBJECT = 'SCHOOLOGY UPLOAD failed'
BODY = 'No values in list.'
sendmail(settings.SCHOOLOGY_TO_EMAIL,
settings.SCHOOLOGY_FROM_EMAIL, BODY, SUBJECT)
csvfile.close()
# renaming old filename to newfilename and move to archive location
shutil.copy(filename, archive_destination)
if not test:
file_download()
def fn_process_second_job(carthid, workercatcode, pcnaggr, jobtitledescr,
positionstart, poseffectend, jobfunctioncode,
supervisorid, rank, fullname, EARL):
engine = get_engine(EARL)
print("In Second Job")
print(pcnaggr)
try:
##############################################################
# Differs from regular job rec routine
# Most of the information has already been validated since this
# job is in the same record row as the primary
# Same supervisor as primary job
# Sane homedept code for the EMPLOYEE
# Same business unit code for the EMPLOYEE
# Same room and building, but probably can ignore since not primary position
# Same worker category code and description, no validation needed
# Payroll company code COULD be different, but shouldn't be
# Job Function Code should be the same
# Split PCN_Aggr (Home Cost Number) into separate components
# first I should determine if this is an insert or update - see if
# pcn_aggr is in the pos_table
# see if pcn code returns a position number
# validate function code in the func_table (Position 2)
# Place dept number in func_area field in position table
# Must validate Division, Dept
# NOT assuming custom field is correct, will notify if no matches
# use PCN Codes to tie employee to job number
##############################################################
###############################################################
# disassmble pcn code into parts JobType, Div, Dept, Job code
###############################################################
print(pcnaggr)
len = pcnaggr.__len__()
pos1 = pcnaggr.find('-', 0)
paycode = pcnaggr[:pos1]
pos2 = pcnaggr.find('-', pos1 + 1, len)
div = pcnaggr[pos1 + 1:pos2]
pos3 = pcnaggr.find('-', pos2 + 1, len)
dept = pcnaggr[pos2 + 1:pos3]
jobnum = pcnaggr[pos3 + 1:len]
spvrID = supervisorid[3:10]
###############################################################
# Use PCN Agg to find TPos FROM position rec
###############################################################
v_tpos = fn_validate_field(pcnaggr, "pcn_aggr", "tpos_no", "pos_table",
"char", EARL)
# if v_tpos == 0 or v_tpos == "" or len(str(v_tpos)) == 0:
if v_tpos == "":
# if v_tpos == None or len(str(v_tpos)) == 0:
print("Position not valid")
raise ValueError()
else:
print("Validated t_pos = " + str(v_tpos))
##############################################################
# validate hrpay, values in this table should not change without
# a project request as they affect a number of things
##############################################################
hrpay_rslt = fn_validate_field(paycode, "hrpay", "hrpay",
"hrpay_table", "char", EARL)
if hrpay_rslt != '':
print('Validated HRPay Code = ' + str(hrpay_rslt) + '\n')
else:
print('Invalid Payroll Company Code ' + str(paycode) + '\n')
fn_write_log(
'Data Error in secondjob.py - Invalid Payroll Company \
Code for secondary job ' + str(paycode) + '\n')
func_code = fn_validate_field(dept, "func", "func", "func_table",
"char", EARL)
if func_code != '':
print('Validated second job func_code = ' + dept + '\n')
else:
print('Invalid Function Code ' + dept + '\n')
fn_write_log('Data Error in second job.py - Invalid Function \
Code = ' + dept + '\n')
##############################################################
# Need some additional info from existing cx records
# ADP does not have field for second job title
##############################################################
q_get_title = '''
SELECT distinct job_rec.job_title
FROM job_rec Where tpos_no = {0}'''.format(v_tpos)
#print(q_get_title)
sql_title = do_sql(q_get_title, key=DEBUG, earl=EARL)
titlerow = sql_title.fetchone()
if titlerow is None:
print("Job Title Not found for tpos " + v_tpos)
jr_jobtitle = ""
fn_write_log('Job Title Not found for secondary job for tpos ' +
str(v_tpos) + '\n')
else:
jr_jobtitle = titlerow[0]
print("Job Title = " + jr_jobtitle)
##############################################################
# validate the position, division, department
##############################################################
# print("....Deal with division...")
hrdivision = fn_validate_field(div, "hrdiv", "hrdiv", "hrdiv_table",
"char", EARL)
if hrdivision == None or hrdivision == "":
print("HR Div not valid - " + div)
# print("....Deal with department...")
hrdepartment = fn_validate_field(dept, "hrdept", "hrdept",
"hrdept_table", "char", EARL)
#print(hrdepartment)
if hrdepartment == None or hrdepartment == "":
print("HR Dept not valid - " + dept)
fn_write_log('Data Error in second job.py - HR Dept not valid ' +
dept + '\n')
# ##############################################################
# If job rec exists for employee in job_rec -update, else insert
# ##############################################################
q_check_exst_job = '''
select job_rec.tpos_no, pos_table.pcn_aggr, job_no
from job_rec, pos_table
where job_rec.tpos_no = pos_table.tpos_no
and job_rec.title_rank = {0}
and job_rec.id = {1}
and (job_rec.end_date is null
or job_rec.end_date > TODAY)
'''.format(rank, carthid)
# print(q_check_exst_job)
sql_exst_job = do_sql(q_check_exst_job, key=DEBUG, earl=EARL)
exst_row = sql_exst_job.fetchone()
if exst_row is None:
print("No Existing secondary jobs")
else:
if exst_row[1] != pcnaggr:
print("Search job = " + pcnaggr)
print("Existing job = " + exst_row[1])
q_end_job = '''update job_rec set end_date = ?
where id = ? and job_no = ?
'''
q_end_job_args = (datetime.now().strftime("%m/%d/%Y"), carthid,
exst_row[2])
print(q_end_job)
print(q_end_job_args)
engine.execute(q_end_job, q_end_job_args)
q_get_job = '''
SELECT job_no
FROM job_rec
WHERE tpos_no = {0}
AND id = {1}
AND (end_date IS null
or end_date > TODAY)
'''.format(v_tpos, carthid, positionstart)
# print(q_get_job)
sql_job = do_sql(q_get_job, key=DEBUG, earl=EARL)
jobrow = sql_job.fetchone()
if jobrow is None:
print("Job Number not found in job rec")
# if no record, no duplicate
# insert
q_ins_job = '''
INSERT INTO job_rec
(tpos_no, descr, bjob_no, id, hrpay, supervisor_no, hrstat,
egp_type, hrdiv, hrdept, comp_beg_date, comp_end_date, beg_date,
end_date, active_ctrct, ctrct_stat, excl_srvc_hrs, excl_web_time,
job_title, title_rank, worker_ctgry)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?)'''
q_ins_job_args = (v_tpos, jr_jobtitle, 0, carthid, paycode, 0,
jobfunctioncode, 'R', div, func_code, None, None,
datetime.now().strftime("%m/%d/%Y"),
None if poseffectend == '' else poseffectend,
'N', 'N/A', 'N', 'N', jobtitledescr, rank,
workercatcode)
# print(q_ins_job + str(q_ins_job_args))
print("New Second Job Record for " + fullname + ', id = ' +
str(carthid))
fn_write_log('New secondary Job Record for ' + fullname +
', id = ' + str(carthid) + '\n')
engine.execute(q_ins_job, q_ins_job_args)
scr.write(q_ins_job + '\n' + str(q_ins_job_args) + '\n')
else:
# jobrow = sql_job.fetchone()
#print('valid job found = ' + str(jobrow[0]))
#print('v_tpos = ' + str(v_tpos) )
q_upd_job = '''
UPDATE job_rec SET descr = ?,
id = ?, hrpay = ?, supervisor_no = ?,
hrstat = ?, hrdiv = ?, hrdept = ?,
beg_date = ?, end_date = ?,
job_title = ?,
title_rank = ?, worker_ctgry = ?
WHERE job_no = ?'''
q_upd_job_args = (jr_jobtitle, carthid,
paycode, 0, jobfunctioncode, div, func_code,
datetime.now().strftime("%m/%d/%Y"),
None if poseffectend == '' else poseffectend,
jobtitledescr, rank, workercatcode, jobrow[0])
# print(q_upd_job)
#print(q_upd_job_args)
engine.execute(q_upd_job, q_upd_job_args)
scr.write(q_upd_job + '\n' + str(q_upd_job_args) + '\n')
print("Update Second Job Record for " + fullname + ', id = ' +
str(carthid))
fn_write_log('Update Job Record for ' + fullname + ', id = ' +
str(carthid) + '\n')
return 1
##############################################################
# Faculty Qualifications - This will go into facqual_rec...
# and qual_table - No longer part of Job Title
# Probably not in scope as these titles do not affect pay
##############################################################
except ValueError:
print("Position not valid for PCN_AGGR " + pcnaggr)
SUBJECT = '[APD To CX Application] Data Error'
BODY = "The Home Cost Number Code is not valid for secondary job. " \
"Code = " + pcnaggr
# sendmail(
# settings.ADP_TO_EMAIL, settings.ADP_FROM_EMAIL,
# BODY, SUBJECT
# )
fn_write_log("The Home Cost Number Code is not valid for secondary "
"job. Code = " + pcnaggr)
except Exception as e:
print("Error in second job for " + fullname + " ID = " + carthid +
" Error = " + e.message)
fn_write_error("Error in second job for " + fullname + " ID = " +
carthid + " Error = " + e.message)
return 0
def main():
try:
# set global variable
global EARL
# determines which database is being called from the command line
if database == 'cars':
EARL = INFORMIX_EARL_PROD
elif database == 'train':
#python address_lookup.py --database=train --test
EARL = INFORMIX_EARL_TEST
elif database == 'sandbox':
#python address_lookup.py --database=sandbox --test
EARL = INFORMIX_EARL_SANDBOX
else:
# this will raise an error when we call get_engine()
# below but the argument parser should have taken
# care of this scenario and we will never arrive here.
EARL = None
# establish database connection
engine = get_engine(EARL)
#----------------------------------------------------
# First go find the records marked for purging
#----------------------------------------------------
q_get_pool = '''SELECT cc_stage_merge_no, prim_id, sec_id, id1, id2,
fullname1, fullname2
FROM cc_stage_merge
WHERE analysis_status not like '%PURGE%'
AND adm_review = 'PURGE'
AND sec_id = 1360472
ORDER BY fullname1
'''
sql_val = do_sql(q_get_pool, key=DEBUG, earl=EARL)
# print(q_get_pool)
if sql_val is not None:
rows = sql_val.fetchall()
for row in rows:
purge_id = row[2]
stage_merge_number = row[0]
# ----------------------------------------------------
# Verify that the sec_id is really the duplicate marked for
# purge
# ----------------------------------------------------
if (row[2] == row[4]) and (str(row[6])[:3] != 'DUP'):
fn_write_log("Sec ID " + str(row[4]) + ", " + str(row[6]) +
" not marked as DUP")
elif (row[2] == row[3]) and (str(row[5])[:3] != 'DUP'):
fn_write_log("Sec ID " + str(row[3]) + ", " + str(row[5]) +
" not marked as DUP")
else:
# ----------------------------------------------------
# Next go find the ID record
# ----------------------------------------------------
if purge_id is not None:
q_get_id_rec = "SELECT fullname, middlename, valid" \
" FROM id_rec " \
"WHERE id = " + str(purge_id)
# print(q_get_id_rec)
sql_val2 = do_sql(q_get_id_rec, key=DEBUG, earl=EARL)
row2 = sql_val2.fetchone()
# print("Row2 value = " + str(row2))
if row2 is not None:
# ------------------------------------------------
# Next update the ID record
# ------------------------------------------------
# print("Name = " + row2[0] + ", Valid = "
# + row2[2] )
if str(row2[0]).find("PURGED") == -1:
q_upd_id_rec = '''UPDATE id_rec SET valid = ?,
fullname = fullname[1, 24]||'(PURGED)'
WHERE id = ?
'''
q_upd_id_rec_args = ('N', purge_id)
print(q_upd_id_rec + ", " +
str(q_upd_id_rec_args))
engine.execute(q_upd_id_rec, q_upd_id_rec_args)
# --------------------------------------------
# Next update the stage merge record
# --------------------------------------------
q_upd_stage = '''UPDATE cc_stage_merge
SET analysis_status = ?,
final_actn_date = TODAY
WHERE
cc_stage_merge_no = ?
and sec_id = ?
'''
q_upd_stage_args = ('PURGECOMPLETE',
stage_merge_number,
purge_id)
print(q_upd_stage + ", " +
str(q_upd_stage_args))
engine.execute(q_upd_stage, q_upd_stage_args)
fn_write_log("ID " + str(purge_id) + ", " +
row2[0] + " Purged.")
else:
fn_write_log("Second ID " + str(purge_id) +
", " + row2[0] +
" already purged")
else:
fn_write_error("Second ID " + str(purge_id) +
", " + " not in id_rec table")
else:
fn_write_error("Null value for secondary ID -"
" no primary chosen")
except Exception as e:
# fn_write_error("Error in zip_distance.py for zip, Error = "
# + e.message)
print("Error in purge_id.py: " + e.message)
def fn_process_job(carthid, workercatcode, workercatdescr, businessunitcode,
businessunitdescr, homedeptcode, homedeptdescr, jobtitlecode,
jobtitledescr, positioneffective, terminationdate, payrollcompcode,
jobfunctioncode, jobfuncdtiondescription, jobclass,
jobclassdescr, primaryposition, supervisorid, last, first,
middle,EARL):
engine = get_engine(EARL)
try:
##############################################################
# must validate ID of supervisor
# Split PCN_Aggr (Home Cost Number) into separate components
# first I should determine if this is an insert or update - see if
# pcn_aggr is in the pos_table
# validate function code in the func_table
# Place dept number in func_area field in position table
# Must account for Division, Dept
# use PCN Codes to tie employee to job number
# validate a number of fields as needed
# add GL Func code to func_area in position table
# if there is a secondary job record, do the same..
##############################################################
# There are records with no job number. Nothing to validate against.
# If Jobtitle_code is empty, end the process with an invalid data message
# print("Job Title Code = " + jobtitlecode + ", " + jobtitledescr
# + ", " + str(terminationdate) + "------------------")
if jobtitlecode is None:
# print("Missing Job Title Code for " + last + "," + first
# + " ID = " + carthid)
raise ValueError("Missing Job Title Code for " + last + ","
+ first + " ID = " + carthid)
elif jobtitlecode == '':
# print("Missing Job Title Code for " + last + "," + first
# + " ID = " + carthid)
raise ValueError("Missing Job Title Code for " + last + ","
+ first + " ID = " + carthid)
# There is a supervisor flag in ADP. But it may not be valid to use
# for validation at this point. Just note.
spvrID = fn_validate_supervisor(supervisorid[3:10], EARL)
# Construct the pcn code from existing items?
# func_area = left(homedeptcode,3)
# hrdept/pcn_03 = homedeptcode[:3]
# pcn_04 = job title code
# hrdiv = business unit code
# pcn_aggr = jobfunctioncode-businessunitcode-homedeptcode-jobtitlecode
pcnaggr = payrollcompcode + "-" + businessunitcode[:4] + "-" \
+ homedeptcode[:3] + "-" + jobtitlecode
print("PCN Aggregate = " + pcnaggr)
func_code = fn_validate_field(homedeptcode[:3],"func","func",
"func_table", "char", EARL)
# print("Function Code = " + str(func_code))
if func_code != '':
fn_write_log("Valid func_code")
# print('Validated func_code = ' + homedeptcode[:3] + '\n')
else:
# print('Invalid Function Code ' + str(homedeptcode[:3]) + '\n')
fn_write_log('Invalid Function Code ' + str(homedeptcode[:3]) + '\n')
# fn_write_error("Error in jobrec.py - Invalid Function Code for ' "
# + id + ' Code = ' + str(homedeptcode[:3]) + '\n');
# print("Error in jobrec.py - Invalid Function Code for " +
# str(carthid) + " Code = " + str(homedeptcode[:3]) + "\n");
# print("Supervisor = " + str(spvrID) +'\n')
##############################################################
# validate hrpay, values in this table should not change without
# a project request as they affect a number of things
##############################################################
hrpay_rslt = fn_validate_field(payrollcompcode,"hrpay","hrpay",
"hrpay_table", "char", EARL)
if hrpay_rslt != '':
#print('Validated HRPay Code = ' + str(hrpay_rslt) + '\n')
fn_write_log('Valid HRPay Code ' + str(hrpay_rslt) + '\n');
else:
#print('Invalid Payroll Company Code ' + str(payrollcompcode) + '\n')
fn_write_error('Error in jobrec.py - Invalid Payroll Company Code '+
str(payrollcompcode) +'\n');
fn_write_error("Error in jobrec.py - Invalid Payroll Company Code " +
str(payrollcompcode) + '\n')
##############################################################
# New table in Informix - Worker Category
# Not maintained in CX, so we will have to maintain it with
# inserts and updates
#############################################################
# print("Worker Cat Code")
v_work_cat_update = fn_needs_update(workercatcode, workercatdescr,
"work_cat_code", "work_cat_descr",
"cc_work_cat_table", "char", EARL)
# print("Work Cat Update = " + str(v_work_cat_update))
if v_work_cat_update == None or len(str(v_work_cat_update)) == 0:
q_ins_wc = '''
INSERT INTO cc_work_cat_table (work_cat_code, work_cat_descr,
active_date)
VALUES (?,?,?)'''
q_ins_wc_args = (workercatcode,workercatdescr,
datetime.now().strftime("%m/%d/%Y"))
# print(q_ins_wc)
# print(q_ins_wc_args)
engine.execute(q_ins_wc, q_ins_wc_args)
fn_write_log("Inserted into cc_work_cat_table, code = " + workercatcode)
scr.write(q_ins_wc + '\n' + str(q_ins_wc_args) + '\n')
else:
'Exists but no match'
if v_work_cat_update[1] != workercatdescr:
q_upd_wc = '''
UPDATE cc_work_cat_table set work_cat_descr = ?
WHERE work_cat_code = ?'''
q_upd_wc_args = (workercatdescr, workercatcode)
# print(q_upd_wc)
# print(q_upd_wc_args)
engine.execute(q_upd_wc, q_upd_wc_args)
fn_write_log("Updated cc_work_cat_table, code = " + workercatcode)
scr.write(q_upd_wc + '\n' + str(q_upd_wc_args) + '\n')
##############################################################
# Job Class Code, HRClass field in Job Rec
##############################################################
if jobclass.strip() != "" and jobclass is not None:
# print(jobclass)
# print(jobclassdescr)
# Find out if class is in the hrclass table
q_hrclass = '''
SELECT *
FROM hrclass_table
WHERE hrclass = "{0}"
AND inactive_date is null'''.format(jobclass)
jclass = do_sql(q_hrclass, key=DEBUG, earl=EARL)
row = jclass.fetchone()
if row is None:
q_hrclass_ins = '''
INSERT INTO hrclass_table
(hrclass, txt, active_date, inactive_date)
VALUES(?, ?, ?, ?)'''
q_hrclass_ins_args = (jobclass, jobclassdescr,
datetime.now().strftime("%m/%d/%Y"),
None)
engine.execute(q_hrclass_ins, q_hrclass_ins_args)
fn_write_log("Inserted into hrclass_table, code = " + jobclass)
scr.write(q_hrclass_ins + '\n' + str(q_hrclass_ins_args) + '\n')
else:
# print(row[1])
if row[1] != jobclassdescr:
q_hrclass_upd = '''
UPDATE hrclass_table
SET txt = ?
WHERE hrclass = ?'''
q_hrclass_upd_args = (jobclassdescr, jobclass)
engine.execute(q_hrclass_upd, q_hrclass_upd_args)
scr.write(q_upd_dept + '\n' + str(q_hrclass_upd_args) + '\n');
fn_write_log("Updated hrclass_table, code = " + jobclass)
else:
#print("No change in HRClass Description")
fn_write_log('There were no changes in HRClass '
'description.\n');
# else:
# print("No Job Class")
##############################################################
# validate the position, division, department
##############################################################
hrdivision = fn_needs_update(str(businessunitcode[:4]), businessunitdescr,
"hrdiv", "descr", "hrdiv_table", "char", EARL)
if hrdivision is None:
q_ins_div = '''
INSERT INTO hrdiv_table(hrdiv, descr, beg_date, end_date)
VALUES(?, ?, ?, null)'''
q_ins_div_args = (businessunitcode[:4], businessunitdescr,
datetime.now().strftime("%m/%d/%Y"))
# print("New HR Division = " + businessunitcode[:4] + '\n')
# print(q_ins_div + str(q_ins_div_args))
fn_write_log(
"Inserted into hrdiv_table, code = " + businessunitcode[:4])
engine.execute(q_ins_div, q_ins_div_args)
scr.write(q_ins_div + '\n' + str(q_ins_div_args) + '\n')
elif hrdivision == "":
q_ins_div = '''
INSERT INTO hrdiv_table(hrdiv, descr, beg_date, end_date)
VALUES(?, ?, ?, null)'''
q_ins_div_args = (businessunitcode[:4], businessunitdescr,
datetime.now().strftime("%m/%d/%Y"))
# print("New HR Division = " + businessunitcode[:4] + '\n')
# print(q_ins_div + str(q_ins_div_args))
fn_write_log("Inserted into hrdiv_table, code = " + businessunitcode[:4])
engine.execute(q_ins_div, q_ins_div_args)
scr.write(q_ins_div + '\n' + str(q_ins_div_args) + '\n')
else:
if hrdivision[1] != businessunitdescr:
# This query works 5/25/18
q_upd_div = '''
UPDATE hrdiv_table SET descr = ?,
beg_date = ?
WHERE hrdiv = ?'''
q_upd_div_args = (businessunitdescr,
datetime.now().strftime("%m/%d/%Y"),
businessunitcode[:4])
# print("Existing HR Division = " + hrdivision[0] + '\n')
# print(q_upd_div + str(q_upd_div_args))
fn_write_log("Updated hrdiv_table, code = " + businessunitcode[:4])
engine.execute(q_upd_div, q_upd_div_args)
scr.write(q_upd_div + '\n' + str(q_upd_div_args) + '\n');
# print("Home Dept Code = " + homedeptcode[:3])
# print("Home Dept descr = " + homedeptdescr)
hrdepartment = fn_needs_update(homedeptcode[:3], homedeptdescr,
"hrdept", "descr", "hrdept_table", "char", EARL)
# print("HR Dept Needs update = " + str(hrdepartment))
if hrdepartment==None or hrdepartment=="" or len(hrdepartment)==0:
# print("Insert Dept")
# This query works 5/25/18
q_ins_dept = '''
INSERT INTO hrdept_table(hrdept, hrdiv, descr,
beg_date, end_date)
VALUES(?, ?, ?, ?, ?)'''
q_ins_dept_args = (homedeptcode[:3], businessunitcode[:4],
homedeptdescr,
datetime.now().strftime("%m/%d/%Y"),None)
# print(q_ins_dept)
# print(q_ins_dept_args)
engine.execute(q_ins_dept, q_ins_dept_args)
fn_write_log("Inserted into hrdept_table, code = " + homedeptcode[:3])
scr.write(q_ins_dept + '\n' + str(q_ins_dept_args) + '\n');
else:
# print("Update Dept")
if hrdepartment[1] != homedeptdescr:
q_upd_dept = '''
UPDATE hrdept_table SET hrdiv = ?, descr = ?,
beg_date = ?
WHERE hrdept = ?'''
q_upd_dept_args = (businessunitcode[:4], homedeptdescr,
datetime.now().strftime("%m/%d/%Y"), func_code)
# print(q_upd_dept)
# print(q_upd_dept_args)
engine.execute(q_upd_dept, q_upd_dept_args)
fn_write_log("Updated hrdept_table, code = " + homedeptcode[:3])
scr.write(q_upd_dept + '\n' + str(q_upd_dept_args) + '\n');
else:
# Need to make sure the department is linked to the division
q_check_dept_div = '''
select hrdiv from hrdept_table where hrdept = {0}
'''.format(homedeptcode[:3])
div_val = do_sql(q_check_dept_div, key=DEBUG, earl=EARL)
if div_val != businessunitcode[:4]:
# print("update dept/div relationship")
q_upd_dept_div = '''
UPDATE hrdept_table SET hrdiv = ?, descr = ?,
beg_date = ?
WHERE hrdept = ?'''
q_upd_dept_div_args = (businessunitcode[:4], homedeptdescr,
datetime.now().strftime("%m/%d/%Y"),
func_code)
# print(q_upd_dept_div)
# print(q_upd_dept_div_args)
engine.execute(q_upd_dept_div, q_upd_dept_div_args)
fn_write_log(
"Updated hrdept_table, code = " + homedeptcode[:3])
scr.write(q_upd_dept_div + '\n' + str(q_upd_dept_div_args) + '\n');
# else:
# print("Home Dept not updated " + homedeptdescr)
###############################################################
# Use PCN Agg to find TPos FROM position rec
###############################################################
print("Find Tpos")
# v_tpos = fn_validate_field(pcnaggr,"pcn_aggr","tpos_no",
# "pos_table","char", EARL)
v_pos = '''
SELECT tpos_no, descr, func_area, hrpay
FROM pos_table
WHERE pcn_aggr = '{0}'
'''.format(pcnaggr)
sql_vtpos = do_sql(v_pos, key=DEBUG, earl=EARL)
# print(sql_vtpos)
row = sql_vtpos.fetchone()
if row == None:
q_ins_pos = '''
INSERT INTO pos_table(pcn_aggr, pcn_01, pcn_02, pcn_03, pcn_04,
descr, ofc, func_area, supervisor_no, tenure_track, fte,
max_jobs, hrpay, active_date, prev_pos)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
q_ins_pos_args = (pcnaggr,payrollcompcode,businessunitcode[:4],
func_code,jobtitlecode, jobtitledescr,
'OFC', func_code, None,
'', 0, 0, payrollcompcode,
datetime.now().strftime("%m/%d/%Y"),'')
# print(q_ins_pos)
# print(q_ins_pos_args)
engine.execute(q_ins_pos, q_ins_pos_args)
fn_write_log("Inserted into pos_table, code = " + pcnaggr)
scr.write(q_ins_pos + '\n' + str(q_ins_pos_args) + '\n')
# Need to return the tpos_no as it is created in the INSERT
#print("New t_pos needed for = " + pcnaggr)
q_get_tpos = '''
SELECT tpos_no
FROM pos_table
WHERE pcn_aggr = '{0}'
'''.format(pcnaggr)
# if tpos exists return the tpos number to find the job record
sql_tpos = do_sql(q_get_tpos, key=DEBUG, earl=EARL)
row = sql_tpos.fetchone()
tpos_result = row[0]
v_tpos = tpos_result
# print("New tpos = " + str(v_tpos))
fn_write_log("New tpos = " + str(v_tpos))
# print(q_ins_pos)
else:
v_tpos = row[0]
# print("v-tpos = " + str(v_tpos))
# print("Existing values =" + row[1] + ", " + row[2] + ", " + row[3])
if row[1] != jobtitledescr or row[2] != func_code or row[3] != payrollcompcode:
# print("Validated t_pos = " + str(v_tpos))
q_upd_pos = '''
UPDATE pos_table SET pcn_aggr = ?, pcn_01 = ?, pcn_02 = ?,
pcn_03 = ?, pcn_04 = ?, descr = ?, ofc = ?, func_area = ?,
supervisor_no = ?, tenure_track = ?, fte = ?, max_jobs = ?,
hrpay = ?, active_date = ?, inactive_date = ?
WHERE tpos_no = ?'''
q_upd_pos_args = (pcnaggr, payrollcompcode, businessunitcode[:4],
func_code, jobtitlecode, jobtitledescr,
'OFC', func_code, None,
'TENURE', 0, 0, payrollcompcode,
datetime.now().strftime("%m/%d/%Y"), None,
v_tpos)
# print(q_upd_pos)
# print(q_upd_pos_args)
fn_write_log("Updated pos_table, code = " + pcnaggr)
engine.execute(q_upd_pos, q_upd_pos_args)
scr.write(q_upd_pos + '\n' + str(q_upd_pos_args) + '\n')
##############################################################
# validate hrstat,
# Per Meeting 6/22/18, Job Function Code redundant and unreliable
# Skip this - Worker Category Code will suffice
##############################################################
# v_job_function_code = fn_validate_field(jobfunctioncode,"hrstat",
# "hrstat", "hrstat_table","char", EARL)
# if v_job_function_code == None or len(v_job_function_code)==0:
# # Insert into hr_stat
# q_ins_stat = '''
# INSERT INTO hrstat_table(hrstat, txt, active_date, inactive_date)
# VALUES(?, ?, ?, null)'''
# q_ins_stat_args = (jobfunctioncode, jobfuncdtiondescription,
# datetime.now().strftime("%m/%d/%Y"))
# engine.execute(q_ins_stat, q_ins_stat_args)
# scr.write(q_ins_stat + '\n' + str(q_ins_stat_args) + '\n');
# else:
# # hrstat_rslt = row[0]
# # valid_hrstat = hrstat_rslt
# print("Existing Job Function Code = " + v_job_function_code)
# q_upd_stat = '''
# UPDATE hrstat_table SET txt = ?
# WHERE hrstat = ?'''
# q_upd_stat_args = (jobfuncdtiondescription, v_job_function_code)
# engine.execute(q_upd_stat, q_upd_stat_args)
# scr.write(q_upd_stat + '\n' + str(q_upd_stat_args) + '\n')
##############################################################
# Determine job rank for job_rec
##############################################################
rank = ''
# print("Primary Position = " + primaryposition + " " + str(terminationdate))
if primaryposition == 'Yes':
rank = 1
elif primaryposition == 'No':
rank = 2
# elif terminationdate.strip != '':
# rank = ''
# print("Rank = " + str(rank) +'\n')
# ##############################################################
# If job rec exists in job_rec -update, else insert
# ##############################################################
print("Do Job Rec")
# Need to add a check here for same job, different department.
# If department changes, then the previous job (PCN AGGR) needs an
# end date - it is in essence a new job
# select job_no from job_rec where id = id and end_date is null
# and hrdept <> homedeptcode[:3]
# If found, end date
q_check_exst_job = '''
select job_rec.tpos_no, pos_table.pcn_aggr, job_no
from job_rec, pos_table
where job_rec.tpos_no = pos_table.tpos_no
and job_rec.title_rank = 1
and job_rec.id = {0}
and (job_rec.end_date is null
or job_rec.end_date > TODAY)
'''.format(carthid)
# print(q_check_exst_job)
sql_exst_job = do_sql(q_check_exst_job, key=DEBUG, earl=EARL)
exst_row = sql_exst_job.fetchone()
if exst_row is None:
print("No Existing primary jobs")
else:
if exst_row[1] != pcnaggr:
# print(pcnaggr)
# print(exst_row[1])
q_end_job = '''update job_rec set end_date = ?
where id = ? and job_no = ?
'''
q_end_job_args = (datetime.now().strftime("%m/%d/%Y"), carthid, exst_row[2])
# print(q_end_job)
# print(q_end_job_args)
engine.execute(q_end_job, q_end_job_args)
scr.write(q_end_job + '\n' + str(q_end_job_args) + '\n')
q_get_job = '''
SELECT job_no
FROM job_rec
WHERE tpos_no = {0}
AND id = {1}
AND (end_date IS null
or end_date > TODAY)
'''.format(v_tpos,carthid,positioneffective)
# Something in the formatting of the date is failing...
# and beg_date = '{2}'
# print(q_get_job)
sql_job = do_sql(q_get_job, key=DEBUG, earl=EARL)
jobrow = sql_job.fetchone()
if jobrow is None:
# print("Job Number not found in job rec")
fn_write_log("Job Number not found in job rec")
# if no record, no duplicate
# insert
# NOTE: NEED TO ADD JOB CLASS CODE into HRCLASS field
# Insert into job rec
# Query works as of 5/29/18
# print("Rank = " + str(rank))
# print("Supervisor = " + str(spvrID) + '\n')
q_ins_job = '''
INSERT INTO job_rec
(tpos_no, descr, bjob_no, id, hrpay, supervisor_no, hrstat,
egp_type, hrdiv, hrdept, comp_beg_date, comp_end_date, beg_date,
end_date, active_ctrct, ctrct_stat, excl_srvc_hrs, excl_web_time,
job_title, title_rank, worker_ctgry, hrclass)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?)'''
q_ins_job_args = (v_tpos, jobtitledescr, 0, carthid,
payrollcompcode, spvrID, '',
'R', businessunitcode[:4], func_code, None, None,
positioneffective,
None if terminationdate == '' else terminationdate,
'N', 'N/A', 'N', 'N',
jobtitledescr, rank, workercatcode, jobclass)
print(q_ins_job + str(q_ins_job_args))
# print("New Job Record for " + last + ', id = ' + str(carthid))
engine.execute(q_ins_job, q_ins_job_args)
fn_write_log("Inserted into job_rec, tpos = " + str(v_tpos)
+ " Description = " + jobtitledescr + " ID = " + str(carthid))
scr.write(q_ins_job + str(q_ins_job_args) + '\n')
else:
# jobrow = sql_job.fetchone()
# print('valid job found = ' + str(jobrow[0]))
fn_write_log('valid job found = ' + str(jobrow[0]))
#print('v_tpos = ' + str(v_tpos) )
q_upd_job = '''
UPDATE job_rec SET descr = ?, id = ?, hrpay = ?,
supervisor_no = ?, hrstat = ?, hrdiv = ?, hrdept = ?,
beg_date = ?, end_date = ?, job_title = ?, worker_ctgry = ?, hrclass = ?
WHERE job_no = ?'''
q_upd_job_args = (jobtitledescr, carthid, payrollcompcode, spvrID,
'', businessunitcode[:4], func_code, positioneffective,
None if terminationdate == '' else terminationdate,
jobtitledescr, workercatcode, jobclass, jobrow[0])
# print("Supervisor = " + str(spvrID) + '\n')
print(q_upd_job)
print(q_upd_job_args)
# print("Update Job Record for " + last + ', id = ' + str(carthid))
engine.execute(q_upd_job, q_upd_job_args)
fn_write_log("Updated job_rec, tpos = " + str(v_tpos)
+ " Description = " + jobtitledescr + " ID = " + str(carthid))
scr.write(q_upd_job + '\n' + str(q_upd_job_args) + '\n')
##############################################################
# Question - on first run, will we need to end date existing jobs that
# are NOT listed in ADP? How to determine which those are if there
# is no match...Figure out in testing
# May be able to query cc_adp_rec and compare that to the job_rec
# table to find jobs NOT listed in ADP but still active in CX
# Not certain we can end date them without further evaluation
##############################################################
##############################################################
# TENURE - This will go into HREMP_REC...
##############################################################
# print("Tenure")
if workercatcode == "T": #tenure
is_tenured = "Y"
is_tenure_track = "N"
elif workercatcode == "TE":
is_tenured = "N"
is_tenure_track = "Y"
else:
is_tenured = "N"
is_tenure_track = "N"
# Same as all others, first have to avoid duplicate by finding out
# if a record already exists
q_get_emp_rec = '''
SELECT id, home_tpos_no
FROM hremp_rec
WHERE id = {0}
'''.format(carthid)
#print(q_get_emp_rec)
sql_emp = do_sql(q_get_emp_rec, key=DEBUG, earl=EARL)
row = sql_emp.fetchone()
if row == None:
#print("No Emp record")
# This query works 5/25/18
q_emp_insert = '''
INSERT into hremp_rec (id, home_tpos_no, ss_first, ss_middle,
ss_last, ss_suffix, tenure, tenure_date, is_tenured,
is_tenure_track)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? )'''
q_emp_ins_args = (carthid, v_tpos, first,middle,last,"","","",
is_tenured,is_tenure_track)
#print(q_emp_insert)
#print(q_emp_ins_args)
#print("Insert into hremp_rec")
engine.execute(q_emp_insert, q_emp_ins_args)
fn_write_log("Inserted into hremp_rec, home_tpos_no = " + str(v_tpos) + " ID = " + str(carthid))
scr.write(q_emp_insert + '\n' + str(q_emp_ins_args) + '\n')
else:
# print('Found Emp Rec')
emp_rslt = row
# print(emp_rslt)
# this query words - 05/25/2018
q_emp_upd = '''
UPDATE hremp_rec SET home_tpos_no = ?, ss_first = ?,
ss_middle = ?, ss_last = ?, ss_suffix = ?, tenure = ?,
tenure_date = ?, is_tenured = ?, is_tenure_track = ?
WHERE id = ?'''
q_emp_upd_args = (v_tpos, first, middle, last, "", "", "",
is_tenured,is_tenure_track, carthid)
#print(q_emp_upd)
#print(q_emp_upd_args)
# print("Update HREMP_REC")
engine.execute(q_emp_upd, q_emp_upd_args)
fn_write_log("Updated hremp_rec, home_tpos_no = " + str(v_tpos)
+ " ID = " + str(carthid))
scr.write(q_emp_upd + '\n' + str(q_emp_upd_args) + '\n');
##############################################################
# Faculty Qualifications - This will go into facqual_rec...
# and qual_table - No longer part of Job Title
# Probably not in scope as these titles do not affect pay
##############################################################
return(1)
except Exception as e:
print(e)
fn_write_error("Error in jobrec.py for " + last + ", " + first
+ ", ID = " + carthid + " Error = " + e.message)
return(0)
def main():
"""
# go to our storage directory on the server
os.chdir(settings.ORGSYNC_CSV_OUTPUT)
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
# sftp connection information
XTRNL_CONNECTION = {
'host':settings.ORGSYNC_HOST,
'username':settings.ORGSYNC_USER,
'private_key':settings.ORGSYNC_PKEY,
'private_key_pass':settings.ORGSYNC_PASS,
'cnopts':cnopts
}
"""
# run SQL statement
sqlresult = do_sql(ORGSYNC_DATA, earl=EARL)
if test:
print(sqlresult)
# set date and time string
datetimestr = time.strftime("%Y%m%d%H%M%S")
# set directory and filename
filename = '{}OrgSync-{}.csv'.format(settings.ORGSYNC_CSV_OUTPUT,
datetimestr)
# create file
phile = open(filename, "w")
output = csv.writer(phile, dialect='excel')
# write header row to file
output.writerow([
"account.username", "account.new_username", "account.email_address",
"account.first_name", "account.last_name", "account.middle_initial",
"account.phone_number", "account.address", "account.city",
"account.state", "account.zip", "account.country", "account.birthday",
"identification_card_number", "portal", "group", "classification",
"form_element.3172627// Gender", "form_element.3172629// Hometown",
"form_element.3172630// Major 1", "form_element.3172630// Major 2",
"form_element.3172630// Major 3", "form_element.3172631// Minor 1",
"form_element.3172631// Minor 2", "form_element.3172631// Minor 3",
"form_element.3172632// Student ID",
"form_element.3172633// Ethnicity",
"form_element.3172634// Projected Graduation Year",
"form_element.3172635// Housing Status",
"form_element.3172636// International Student",
"form_element.3172637// Transfer Student",
"form_element.3451747// Building",
"form_element.3451748// Room Number", "form_element.3451749// Mailbox"
])
# write data rows to file
if sqlresult is not None:
for row in sqlresult:
if test:
print(row)
output.writerow(row)
else:
print("No values in list")
phile.close()
"""
