def fn_insert_aa(id, fullname, aa, addr1, addr2, addr3, cty, st, zp, ctry,
beg_date, phone, EARL):
# print("AA = " + aa + " ID = " + str(id)) + ", Name = " + fullname
try:
engine = get_engine(EARL)
# print(beg_date)
q_insert_aa = '''
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_aa_args = (id, aa, beg_date, "N", "", addr1, addr2, addr3, cty,
st, zp, ctry, phone, "", "HR", "", "")
engine.execute(q_insert_aa, q_ins_aa_args)
scr.write(q_insert_aa + '\n' + str(q_ins_aa_args))
fn_write_log("Added " + addr1 + " to aa_rec for " + fullname +
", ID = " + str(id))
# logger.info("Added archive address for " + fullname);
# print(q_insert_aa)
# print(q_ins_aa_args)
# print("insert aa completed")
except Exception as e:
# print("Error in aarec.py, fn_insert_aa. for ID " + str(id) + ", Name "
# + fullname + " Error = " + e.message)
fn_write_error("Error in aarec.py, fn_insert_aa. for ID " + str(id) +
", Name " + fullname + " Error = " + e.message)
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 file_download():
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
# External connection information for ADP Application server
XTRNL_CONNECTION = {
'host': settings.ADP_HOST,
'username': settings.ADP_USER,
'password': settings.ADP_PASS,
'cnopts': cnopts
}
############################################################################
# sFTP GET downloads the CSV file from ADP server and saves in local directory.
############################################################################
try:
with pysftp.Connection(**XTRNL_CONNECTION) as sftp:
sftp.chdir("adp/")
# Remote Path is the ADP server and once logged in we fetch directory listing
remotepath = sftp.listdir()
# Loop through remote path directory list
for filename in remotepath:
remotefile = filename
# set local directory for which the ADP file will be downloaded to
local_dir = ('{0}'.format(settings.ADP_CSV_OUTPUT))
localpath = local_dir + remotefile
# GET file from sFTP server and download it to localpath
sftp.get(remotefile, localpath)
#############################################################
# Delete original file %m_%d_%y_%h_%i_%s_Applications(%c).txt
# from sFTP (ADP) server
#############################################################
# sftp.remove(filename)
sftp.close()
# file_download()
except Exception as e:
# print(e)
fn_write_error("Error in adp_ftp.py - Error = " + e.message)
return (0)
def fn_end_date_aa(id, aa_num, fullname, enddate, aa, EARL):
engine = get_engine(EARL)
try:
q_enddate_aa = '''
UPDATE aa_rec
SET end_date = ?, aa = ?
WHERE id = ?
AND aa_no = ?'''
q_enddate_aa_args = (enddate, aa, id, aa_num)
engine.execute(q_enddate_aa, q_enddate_aa_args)
# print("Log end date aa for " + fullname)
fn_write_log("Added end date to address to aa_rec for " + fullname +
", ID = " + str(id) + " aa_num = " + str(aa))
# logger.info("Log end date aa_rec for " + fullname);
scr.write(q_enddate_aa + '\n' + str(q_enddate_aa_args) + '\n')
# print(q_enddate_aa)
# print(q_enddate_aa_args)
# print("end Date aa completed")
return (1)
except Exception as e:
fn_write_error("Exception in aarec.py fn_end_date_aa, error = " +
e.message)
return (0)
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 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_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 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_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 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_process_cvid(carthid, adpid, ssn, adp_assoc_id, EARL):
engine = get_engine(EARL)
try:
##############################################################
# Inserts or updates as needed into cvid_rec
##############################################################
# Validate the cx_id
v_cx_id = fn_validate_field(carthid, "cx_id", "cx_id", "cvid_rec",
"integer", EARL)
#print("CX_ID = " + str(carthid))
#print("v_CX_ID = " + str(v_cx_id))
# Should also check for duplicates of the adp_id and associate_id
# What to do in that case?
v_adp_match = fn_check_duplicates(adpid, "adp_id", "cx_id", "cvid_rec",
v_cx_id, "char", EARL)
#print("Found_ID = " + str(v_adp_match))
# By definition, associate ID cannot be a duplicate in ADP, but
# possibility of duplicate might exist in CX
v_assoc_match = fn_check_duplicates(adp_assoc_id, "adp_associate_id",
"cx_id", "cvid_rec", v_cx_id,
"char", EARL)
#print("Found ID = " + str(v_assoc_match))
if v_cx_id == 0 and v_assoc_match == 0 and v_adp_match == 0:
# Insert or update as needed to ID_rec
q_insert_cvid_rec = '''
INSERT INTO cvid_rec (old_id, old_id_num, adp_id, ssn, cx_id,
cx_id_char, adp_associate_id)
VALUES (?,?,?,?,?,?,?)'''
q_insert_cvid_args = (carthid, carthid, adpid, ssn, carthid,
carthid, adp_assoc_id)
# print(q_insert_cvid_rec)
engine.execute(q_insert_cvid_rec, q_insert_cvid_args)
scr.write(q_insert_cvid_rec + '\n' + str(q_insert_cvid_args) +
'\n')
fn_write_log("Inserted into cvid_rec table, all cx ID fields = " +
str(v_cx_id) + ", ADP ID = " + str(adpid) +
", Associate ID + " + adp_assoc_id)
elif str(v_cx_id) != v_assoc_match and v_assoc_match != 0:
print('Duplicate Associate ID found')
fn_write_log('Duplicate Associate ID found' + carthid)
elif str(v_cx_id) != str(v_adp_match) and v_adp_match != 0:
fn_write_log('Duplicate Associate ID found' + carthid)
print('Duplicate ADP ID found')
else:
q_update_cvid_rec = '''
UPDATE cvid_rec
SET old_id = ?, old_id_num = ?, adp_id = ?, ssn = ?,
adp_associate_id = ?
WHERE cx_id = ?'''
q_update_cvid_args = (carthid, carthid, adpid, ssn, adp_assoc_id,
carthid)
# print(q_update_cvid_rec)
fn_write_log("Updated cvid_rec table, all cx id fields = " +
str(v_cx_id) + ", ADP ID = " + str(adpid) +
", Associate ID = " + adp_assoc_id)
# logger.info("Update cvid_rec table");
scr.write(q_update_cvid_rec + '\n' + str(q_update_cvid_args) +
'\n')
engine.execute(q_update_cvid_rec, q_update_cvid_args)
return 1
except Exception as e:
print(e)
fn_write_error("Error in cvidrec.py for " + carthid + " Error = " +
e.message)
return 0
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():
# set start_time in order to see how long script takes to execute
start_time = time.time()
##########################################################################
# development server (bng), you would execute:
# ==> python adptocx.py --database=train --test
# production server (psm), you would execute:
# ==> python adptocx.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
if database == 'train':
EARL = INFORMIX_EARL_TEST
elif database == 'sandbox':
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)
# set date and time to be added to the filename
datetimestr = time.strftime("%Y%m%d%H%M%S")
# set local directory for which the common app file will be downloaded to
source_dir = ('{0}'.format(settings.ADP_CSV_OUTPUT))
# Defines file names and directory location
new_adp_file = ('{0}ADPtoCX.csv'.format(settings.ADP_CSV_OUTPUT))
last_adp_file = ('{0}ADPtoCXLast.csv'.format(settings.ADP_CSV_OUTPUT))
adp_diff_file = ('{0}different.csv'.format(settings.ADP_CSV_OUTPUT))
# First remove yesterdays file of updates
if os.path.isfile(adp_diff_file):
os.remove(adp_diff_file)
try:
#################################################################
# STEP 0--
# Pull the file from the ADP FTP site
# execute sftp code that needs to be executed in production only
#################################################################
# if not test:
# file_download()
#################################################################
# STEP 1--
# Read files and write out differences
#################################################################
#
# # Need to delete the differences file to start fresh
# if os.path.isfile(adp_diff_file):
# os.remove(adp_diff_file)
#
# # Read in both files and compare
# # the codecs function prevents the header from ADP getting
# # into the comparison - needed because of extra characters in header
# with codecs.open(new_adp_file, 'r',
# encoding='utf-8-sig') as t1, codecs.open(last_adp_file,
# 'r', encoding='utf-8-sig') as t2:
#
# newfile = t1.readlines()
# oldfile = t2.readlines()
#
# # This uses sets to compare the two files
# # returns additions or changes in new but not in original
# bigb = set(newfile) - set(oldfile)
#
# # Write differences to output file
# with open(adp_diff_file, 'wb') as file_out:
# # Write header row
# csvWriter = csv.writer(file_out)
# csvWriter.writerow(
# ["file_number", "carth_id", "last_name", "first_name",
# "middle_name", "salutation", "payroll_name",
# "preferred_name", "birth_date", "gender", "marital_status",
# "race", "race_descr", "ethnicity", "ethnicity_id_meth",
# "personal_email", "primary_address1", "primary_address2",
# "primary_address3", "primary_city", "primary_state_code",
# "primary_state_descr", "primary_zip", "primary_county",
# "primary_country", "primary_country_code",
# "primary_legal_address", "home_phone", "mobile_phone",
# "work_phone", "wc_work_phone", "wc_work_email",
# "use_work_for_notification", "legal_address1",
# "legal_address2", "legal_address3", "legal_city",
# "legal_state_code", "legal_state_description", "legal_zip",
# "legal_county", "legal_country", "legal_country_code",
# "ssn", "hire_date", "hire_rehire_date", "rehire_date",
# "pos_start_date", "pos_effective_date",
# "pos_effective_end_date", "termination_date",
# "position_status", "status_effective_date",
# "status_eff_end_date", "adj_service_date", "archived",
# "position_id", "primary_position", "payroll_comp_code",
# "payroll_comp_name", "cip", "worker_cat_code",
# "worker_cat_descr", "job_title_code", "job_title_descr",
# "home_cost_code", "home_cost_descr", "job_class_code",
# "job_class_descr", "job_description", "job_function_code",
# "job_function_description", "room_number", "location_code",
# "location_description", "leave_start_date",
# "leave_return_date",
# "home_dept_code", "home_dept_descr", "supervisor_id",
# "supervisor_fname", "supervisor_lname","business_unit_code",
# "business_unit_descr","reports_to_name","reports_to_pos_id",
# "reports_to_assoc_id", "employee_assoc_id",
# "management_position", "supervisor_flag", "long_title"])
#
# for line_no, line in enumerate(bigb):
# x = line.split(',')
# file_out.write(line)
# # print('File = ' + x[0] + ', ID = ' + x[
# # 1] + ', First = ' + x[3] + ', Last = ' + x[6])
#
# # close the files
# t1.close()
# t2.close()
# file_out.close()
#
# scr.write('-------------------------------------------------------\n')
# scr.write('-- CREATES APPLICATION FROM APD TO CX DATA \n')
# scr.write('-------------------------------------------------------\n')
#################################################################
# STEP 2--
# Open differences file and start loop through records
#################################################################
with open(adp_diff_file, 'r') as f:
d_reader = csv.DictReader(f, delimiter=',')
adpcount = 0
ccadpcount = 0
idcount = 0
cvidcount = 0
emailcount = 0
phonecount = 0
jobcount = 0
profilecount = 0
secondjobcount = 0
for row in d_reader:
print('--------------------------------------------------')
print('carthid = {0}, Fullname = {1}'.format(
row["carth_id"], row["payroll_name"]))
# print("Use as legal {0}".format(row["primary_legal_address"]))
ethnic_code = {
'Not Hispanic or Latino': 'N',
'Hispanic or Latino': 'Y'
}
is_hispanic = ethnic_code.get(row["ethnicity"])
if is_hispanic is None:
is_hispanic = ""
# else:
# print("Is Hispanic = " + str(is_hispanic))
racecode = {
'1': 'WH',
'2': 'BL',
'4': 'AS',
'5': 'AM',
'6': 'AP',
'9': 'MU'
}
race = racecode.get(row["race"])
if race is None:
race = ""
# print("Race = " + str(race))
# else:
##############################################################
# STEP 2a--
# Write entire row to cc_adp_rec table
##############################################################
#
# try:
# q_cc_adp_rec = ("INSERT INTO cc_adp_rec (file_no, \
# carthage_id, lastname, firstname, middlename, \
# salutation, fullname, pref_name, birth_date, gender, \
# marital_status, race, \
# race_descr, hispanic, race_id_method, personal_email, \
# primary_addr_line1, primary_addr_line2, \
# primary_addr_line3, primary_addr_city, primary_addr_st, \
# primary_addr_state, primary_addr_zip, \
# primary_addr_county, primary_addr_country, \
# primary_addr_country_code, primary_addr_as_legal, \
# home_phone, cell_phone, work_phone, \
# work_contact_phone, work_contact_email, \
# work_contact_notification, \
# legal_addr_line1, legal_addr_line2, legal_addr_line3, \
# legal_addr_city, legal_addr_st, legal_addr_state, \
# legal_addr_zip, legal_addr_county, legal_addr_country, \
# legal_addr_country_code, ssn, hire_date, \
# hire_rehire_date, rehire_date, position_start_date, \
# position_effective_date, position_effective_end_date, \
# termination_date, position_status, status_effective_date, \
# status_effective_end_date, adjusted_service_date, \
# archived_employee, position_id, primary_position, \
# payroll_company_code, payroll_company_name, \
# cip_code, worker_category_code, worker_category_descr, \
# job_title_code, job_title_descr, home_cost_number_code, \
# home_cost_number_descr, job_class_code, job_class_descr, \
# job_descr, job_function_code, \
# job_function_descr, room, bldg, bldg_name, \
# leave_of_absence_start_date, \
# leave_of_absence_return_date, \
# home_depart_num_code, home_depart_num_descr, \
# supervisor_id, supervisor_firstname, supervisor_lastname, \
# business_unit_code, business_unit_descr, reports_to_name, \
# reports_to_position_id, reports_to_associate_id, \
# employee_associate_id, management_position, \
# supervisor_flag, long_title, date_stamp) \
# VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
# ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
# ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
# ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
# ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
# ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
# cc_adp_args = (row["file_number"], row["carth_id"],
# row["last_name"],
# row["first_name"], row["middle_name"], row["salutation"],
# row["payroll_name"], row["preferred_name"],
# fn_convert_date(row["birth_date"]), (row["gender"][:1]),
# row["marital_status"], race,
# row["race_descr"], is_hispanic,
# row["ethnicity_id_meth"], row["personal_email"],
# row["primary_address1"], row["primary_address2"],
# row["primary_address3"], row["primary_city"],
# row["primary_state_code"], row["primary_state_descr"],
# row["primary_zip"], row["primary_county"],
# row["primary_country"], row["primary_country_code"],
# (row["primary_legal_address"][:1]),
# fn_format_phone(row["home_phone"]),
# fn_format_phone(row["mobile_phone"]),
# fn_format_phone(row["work_phone"]),
# fn_format_phone(row["wc_work_phone"]), row["wc_work_email"],
# (row["use_work_for_notification"][:1]),
# row["legal_address1"],
# row["legal_address2"], row["legal_address3"],
# row["legal_city"], row["legal_state_code"],
# row["legal_state_description"], row["legal_zip"],
# row["legal_county"], row["legal_country"],
# row["legal_country_code"], row["ssn"],
# row["hire_date"],
# fn_convert_date(row["hire_rehire_date"]),
# fn_convert_date(row["rehire_date"]),
# fn_convert_date(row["pos_start_date"]),
# fn_convert_date(row["pos_effective_date"]),
# fn_convert_date(row["pos_effective_end_date"]),
# fn_convert_date(row["termination_date"]),
# row["position_status"],
# fn_convert_date(row["status_effective_date"]),
# fn_convert_date(row["status_eff_end_date"]),
# fn_convert_date(row["adj_service_date"]),
# row["archived"], row["position_id"],
# row["primary_position"], row["payroll_comp_code"],
# row["payroll_comp_name"], row["cip"],
# row["worker_cat_code"], row["worker_cat_descr"],
# row["job_title_code"], row["job_title_descr"],
# row["home_cost_code"], row["home_cost_descr"],
# row["job_class_code"], row["job_class_descr"],
# row["job_description"], row["job_function_code"],
# row["job_function_description"], row["room_number"],
# row["location_code"], row["location_description"],
# fn_convert_date(row["leave_start_date"]),
# fn_convert_date(row["leave_return_date"]),
# row["home_dept_code"], row["home_dept_descr"],
# row["supervisor_id"], row["supervisor_fname"],
# row["supervisor_lname"], row["business_unit_code"].zfill(3),
# row["business_unit_descr"], row["reports_to_name"],
# row["reports_to_pos_id"], row["reports_to_assoc_id"],
# row["employee_assoc_id"], row["management_position"],
# row["supervisor_flag"], row["long_title"],
# datetime.now())
# # print(q_cc_adp_rec)
# # print(cc_adp_args)
# engine.execute(q_cc_adp_rec, cc_adp_args)
# # ccadpcount =+ 1
# scr.write(q_cc_adp_rec + '\n' + str(cc_adp_args) + '\n');
# fn_write_log("Inserted data into cc_adp_rec table for "
# + row["payroll_name"] + " ID = "
# + row["carth_id"]);
#
# ccadpcount = ccadpcount + 1
# except Exception as e:
# fn_write_error("Error in adptcx.py while inserting into"
# " cc_adp_rec. Error = " + e.message)
# continue
# # print(e)
#
# # fn_convert_date(row["termination_date"]),
##############################################################
# STEP 2b--
# Do updates to id_rec
# Note - we may have to deal with addresses separately from
# basic demographic information
##############################################################
# If ADP File is missing the Carthage ID, we cannot process the
# record - For ALL in file
# email HR if CarthID is missing
print("In ID Rec sub")
if row["carth_id"] == "":
# print('No Carthage ID - abort this record and email HR')
SUBJECT = 'No Carthage ID - abort this record and email HR'
BODY = 'No Carthage ID, process aborted. Name = {0}, \
ADP File = {1}' .format(row["payroll_name"], \
row["file_number"])
# sendmail(settings.ADP_TO_EMAIL, settings.ADP_FROM_EMAIL,
# BODY, SUBJECT
# )
fn_write_log('There was no carthage ID in file, row \
skipped. Name = {0}, \
ADP File = {1}' .format(row["payroll_name"], \
row["file_number"]))
else:
# Check to see if record exists in id_rec
# Do this first, or everything else is moot
results = fn_validate_field(row["carth_id"], "id", "id",
"id_rec", "integer", EARL)
# print("ID Validate result = " + str(results))
if results is None:
SUBJECT = 'No matcining ID in id_rec - abort and ' \
'email HR'
BODY = 'No matching ID in id_rec, process aborted. ' \
'Name = {0}, \
ADP File = {1}' .format(
fullname, file_number)
#print(SUBJECT)
# sendmail(settings.ADP_TO_EMAIL,
# settings.ADP_FROM_EMAIL, BODY, SUBJECT)
fn_write_log('There was no matching ID in id_Rec \
table, row skipped. Name '
'= {0}, \
ADP File = {1}'.format(
fullname, file_number))
# We should theoretically never insert an ID record
# unless at some point they don't need the cx_id in
# ADP and can let CX create new ID numbers
# programatically
#
# fn_process_idrec(row["carth_id"], row["file_number"],
# row["payroll_name"],
# row["last_name"], row["first_name"],
# row["middle_name"], row["salutation"],
# row["primary_address1"],
# row["primary_address2"],
# row["primary_address3"],
# row["primary_city"],
# row["primary_state_code"],
# row["primary_zip"], row["primary_country"],
# row["ssn"], row["home_phone"],
# row["position_status"],
# fn_convert_date(row["hire_date"]))
# Use hire date if we do the initial insert...
## fn_convert_date(row["pos_effective_date"]))
# Student employees should have cvid_rec for
# provisioning
# Initial test done against cx 6/12/18
else:
# Exclude student employees from main process,
# paycode DPW
if row["payroll_comp_code"] != 'DPW':
########################################
# This will take care of addresses and demographics
########################################
print("Deal with Address")
# print("Home Phone = "
# + fn_format_phone(row["home_phone"]))
id_rslt = fn_process_idrec(
row["carth_id"],
row["file_number"],
row["payroll_name"],
row["last_name"],
row["first_name"],
row["middle_name"],
row["salutation"],
row["primary_address1"],
row["primary_address2"],
row["primary_address3"],
row["primary_city"],
row["primary_state_code"],
row["primary_zip"],
row["primary_country"],
row["primary_country_code"],
row["ssn"],
fn_format_phone(row["home_phone"]),
# ("" if None else
row["position_status"],
fn_convert_date(row["pos_effective_date"]),
EARL)
# print(id_rslt)
# print("ID Result = " + str(id_rslt))
# idcount = idcount + 1
# print("sql addr " + addr_result[1].strip() + " loop
# address = " + row["primary_address1"].strip())
# print("Email 2 = " + row["personal_email"])
# print("Email 3 = " + row["wc_work_email"])
if row["personal_email"] != '' and \
row["wc_work_email"] is not None:
email_result = fn_set_email(
row["personal_email"], row["carth_id"],
row["payroll_name"], "EML2", EARL)
print("Email = " + str(email_result))
# if email_result.strip == "":
#
# elif email_result is None:
# else
# emailcount = emailcount + 1
# else:
# we can remove the else
# print("No personal email from ADP")
if row["wc_work_email"] != '' \
and row["wc_work_email"] is not None:
email_result = fn_set_email(
row["wc_work_email"], row["carth_id"],
row["payroll_name"], "EML3", EARL)
# print("Email3 = " + str(email_result))
# if email_result != "":
# emailcount = emailcount + 1
# Check to update phone in aa_rec
if row["mobile_phone"] != "":
cell = fn_set_cell_phone(
fn_format_phone(row["mobile_phone"]),
row["carth_id"], row["payroll_name"], EARL)
if cell != "":
phonecount = phonecount + 1
#print("Cell phone result: " + cell)
#################################################
# STEP 2c--
# Do updates to profile_rec (profilerec.py)
#################################################
print("In Profile Rec")
prof_rslt = fn_process_profile_rec(
row["carth_id"], is_hispanic, row["gender"],
race, row["birth_date"],
datetime.now().strftime("%m/%d/%Y"), EARL)
# profilecount = profilecount + prof_rslt
print("Profile Result = " + str(prof_rslt))
#################################################
# STEP 2d--
# Do updates to cvid_rec (cvidrec.py)
##################################################
print("In CVID_REC")
cvid_rslt = fn_process_cvid(
row["carth_id"], row["file_number"],
row["ssn"], row["employee_assoc_id"], EARL)
cvidcount = cvidcount + cvid_rslt
##################################################
# STEP 2e--
# Do updates to job_rec (jobrec.py)
##################################################
print("In Job Rec")
job_rslt = fn_process_job(
row["carth_id"], row["worker_cat_code"],
row["worker_cat_descr"],
row["business_unit_code"].zfill(3),
row["business_unit_descr"],
row["home_dept_code"], row["home_dept_descr"],
row["job_title_code"], row["job_title_descr"],
row["pos_effective_date"],
row["termination_date"],
row["payroll_comp_code"],
row["job_function_code"],
row["job_function_description"],
row["job_class_code"], row["job_class_descr"],
row["primary_position"], row["supervisor_id"],
row["last_name"], row["first_name"],
row["middle_name"], EARL)
# print("Process Job Returned " + str(job_rslt))
jobcount = jobcount + job_rslt
# ###############################################
# # STEP 2f--
# # Do updates to second job_rec (jobrec.py)
# ###############################################
# print("In secondary Job Rec")
#
# if row["home_cost_number2"] != '':
# fn_process_second_job(row["carth_id"],
# row["worker_cat_code"],
# row["home_cost_number2"],
# row["job_title_descr"],
# row["position_eff_date2"],
# row["position_end_date2"],
# row["job_function_code"],
# row["supervisor_id"], 3,
# row["payroll_name"], EARL)
# secondjobcount = secondjobcount + 1
# print("Second Job for " + row["carth_id"]
# + " Job = " + row["home_cost_number2"])
# elif row["home_cost_number3"] != '':
# fn_process_second_job(
# row["carth_id"],
# row["worker_cat_code"],
# row["home_cost_number3"],
# row["job_title_descr"],
# row["position_eff_date3"],
# row["position_end_date3"],
# row["job_function_code"],
# row["supervisor_id"], 4,
# row["payroll_name"], EARL)
# secondjobcount = secondjobcount + 1
#
# elif row["home_cost_number4"] != '':
# fn_process_second_job(
# row["carth_id"],
# row["worker_cat_code"],
# row["home_cost_number4"],
# row["job_title_descr"],
# row["position_eff_date4"],
# row["position_end_date4"],
# row["job_function_code"],
# row["supervisor_id"], 5,
# row["payroll_name"], EARL)
# secondjobcount = secondjobcount + 1
##################################################
# STEP 2g--
# Add SCHL record to aa_rec
# (Directory Name - Location)
##################################################
# Check to see if one exists
# If not write new
# May include carthage work phone, ext,
# building code and room (LH 444)
print("Begin Schl record process")
loc_code = {
'1': 'LH',
'2': 'CC',
'3': 'DSC',
'4': 'HL',
'5': 'JAC',
'6': 'TWC',
'7': 'TC',
'11': 'MADR',
'15': 'SC',
'16': 'TA'
}
#print(str(loc_code))
loc = loc_code.get(row["location_code"])
#print("loc = " + str(loc))
fn_set_schl_rec(row["carth_id"],
row["payroll_name"], "", "", loc,
row["room_number"], EARL)
######################################################
# Finally for student employees
#####################################################
else:
################################################
# Do updates to cvid_rec (cvidrec.py) for students
# for provisioning
################################################
fn_process_cvid(row["carth_id"],
row["file_number"], row["ssn"],
row["employee_assoc_id"], EARL)
fn_clear_logger()
adpcount = adpcount + 1
# set destination directory for which the sql file
# will be archived to
archived_destination = ('{0}apdtocx_output-{1}.sql'.format(
settings.ADP_CSV_ARCHIVED, datetimestr))
# set name for the sqloutput file
sqloutput = ('{0}/apdtocx_output.sql'.format(os.getcwd()))
# Check to see if sql file exists, if not send Email
if os.path.isfile("apdtocx_output.sql") != True:
# there was no file found on the server
SUBJECT = '[APD To CX Application] failed'
BODY = "There was no .sql output file to move."
# sendmail(
# settings.ADP_TO_EMAIL,settings.ADP_FROM_EMAIL,
# BODY, SUBJECT
# )
fn_write_log("There was no .sql output file to move.")
else:
# rename and move the file to the archive directory
shutil.move(sqloutput, archived_destination)
##################################################################
# The last step - move last to archive, rename new file to _last
##################################################################
if not test:
adptocx_archive = ('{0}adptocxlast_{1}.csv'.format(
settings.ADP_CSV_ARCHIVED, datetimestr))
shutil.move(last_adp_file, adptocx_archive)
adptocx_rename = ('{0}ADPtoCXLast.csv'.format(
settings.ADP_CSV_OUTPUT))
shutil.move(new_adp_file, adptocx_rename)
print("---------------------------------------------------------")
print("ADP Count = " + str(adpcount))
print("CCADP Count = " + str(ccadpcount))
print("ID Count = " + str(idcount))
print("CVID Count = " + str(cvidcount))
print("Email Count = " + str(emailcount))
print("CELL Count = " + str(phonecount))
print("Job Count = " + str(jobcount))
print("Profile Count = " + str(profilecount))
print("Job2 Count = " + str(secondjobcount))
except Exception as e:
fn_write_error("Error in adptocx.py, Error = " + e.message)
print(e)
def fn_process_profile_rec(id, ethnicity, sex, race, birth_date,
prof_last_upd_date, EARL):
engine = get_engine(EARL)
try:
##########################################################
# Find out if record exists to determine update vs insert
##########################################################
prof_rslt = fn_validate_field(id, "id", "id", "profile_rec", "integer",
EARL)
print("Prof Result = " + str(prof_rslt))
# create race dictionary
if race is None:
# fn_write_error("Race is None")
v_race = 'UN'
else:
v_race = race
# create ethnicity dictionary
if ethnicity is None:
is_hispanic = 'N'
# elif ethnicity == '':
# is_hispanic = 'N'
else:
is_hispanic = ethnicity
# print(is_hispanic)
if birth_date is None or birth_date.strip() == "" or len(
birth_date) == 0:
b_date = None
print("Empty Birthdate")
age = None
else:
age = fn_calculate_age(birth_date)
b_date = birth_date
# print("Age = " + str(age))
if prof_rslt is None or prof_rslt == 0:
# Insert or update as needed
q_insert_prof_rec = '''
INSERT INTO profile_rec (id, sex, race, hispanic, birth_date,
age, prof_last_upd_date)
VALUES (?, ?, ?, ?, ?, ?, ?) '''
q_ins_prof_args = (id, sex, v_race, is_hispanic, b_date, age,
prof_last_upd_date)
# print(q_insert_prof_rec)
# print(q_ins_prof_args)
engine.execute(q_insert_prof_rec, q_ins_prof_args)
fn_write_log("Inserted into profile_rec table values " + str(id) +
", " + v_race + ", " + str(is_hispanic))
# print("Inserted into profile_rec table values " + str(id) + ","
# + v_race + ", " + str(is_hispanic))
scr.write(q_insert_prof_rec + '\n' + str(q_ins_prof_args) + '\n')
else:
q_update_prof_rec = '''
UPDATE profile_rec SET sex = ?,
hispanic = ?, race = ?,
birth_date = ?, age = ?,
prof_last_upd_date = ?
WHERE id = ?'''
q_upd_prof_args = (sex, is_hispanic, v_race, b_date, age,
prof_last_upd_date, id)
# print(q_update_prof_rec)
# print(q_upd_prof_args)
engine.execute(q_update_prof_rec, q_upd_prof_args)
fn_write_log("Updated profile_rec table values " + str(id) + "," +
v_race + ", " + str(is_hispanic))
scr.write(q_update_prof_rec + '\n' + str(q_upd_prof_args) + '\n')
return 1
except Exception as e:
print(e)
fn_write_error("Error in profilerec.py for ID " + str(id) +
", Error = " + e.message)
return 0