def processfile(inputfile):
report = []
report_new = []
report_error = []
report_closed = []
ssnIdx = midIdx = -1
sheet = 0
print "\n\nWorking on " + inputfile
wb = xlrd.open_workbook(inputfile)
sh = SelectSheet(wb)
# initilize
getdatabase()
# get company
company = IdentifyCompany(inputfile)
print "\nUsing company: " + company.getname()
# get operational mode (deal or settlement)
mode = getmode()
# get deal date
date = getdate()
# get running level
level = getlevel()
# load data based on company
print "\nLoading employees, schemes and fund owners (as needed)..."
schemes, internal, holdercomp, shareclass = po.Load(company, level, date)
# label columns
startrow, endrow, colnames = IdentifyColumns(sh)
# process contributions
print "\n\nProcessing Contributions"
print "------------------------"
names = getEmployeeNames(sh, startrow, endrow, colnames)
if "Social Security Number" in colnames:
ssnIdx = colnames.index("Social Security Number")
if "Staff id" in colnames:
midIdx = colnames.index("Staff id")
if ssnIdx < 0 and midIdx < 0:
print "\n\nCannot process file: please make sure Social Security Information or Member Ids are included in the contributions report"
sys.exit(2)
uniq, duplicates = IsUniqueId(sh, startrow, endrow, ssnIdx, midIdx,
colnames)
if not uniq:
err = "\n\nCannot process file: duplicates exist. Please make sure Social Security Information is unique for all employees: %s" % duplicates
print err
sys.exit(2)
# get contributions
for rownum in range(startrow, endrow):
# if no name, skip - line is probably blank
if names[rownum] == '':
#print "Skipping line "+str(rownum+1)
continue
# if no numbers provided for any of the contribution columns, skip
ml = [
'Tier 2 (5%)', 'Pre-Employer Contributions',
'Pre-Employee Contributions', 'Post-Employer Contributions',
'Post-Employee Contributions'
]
validline = True
for c in ml:
if c in colnames:
cIdx = colnames.index(c)
check = isinstance(sh.cell_value(rownum, cIdx),
int) or isinstance(
sh.cell_value(rownum, cIdx), float)
if not check:
validline = False
if not validline:
print "Skipping line " + str(rownum + 1)
continue
ssn = sh.cell_value(rownum, ssnIdx) if ssnIdx > -1 else ''
mid = sh.cell_value(rownum, midIdx) if midIdx > -1 else ''
mainid = ssn if ssn != '' else mid
mainid = cleanid(mainid)
employee = GetEmployee(ssn, mid)
new = 0
n = names[rownum] if employee is None else employee.getkey()
new = 1 if employee is None else 0
if employee is None:
x = None
for mye in po.employees:
syname = fixname(mye.getname())
dbname = set(syname.split())
match = dbname.intersection((names[rownum]).split())
ngd = True if len(match) >= 2 else False
if ngd is True:
try:
eid = mye.getssid() if ssnIdx > -1 else mye.geteid()
eid = fixerrors(eid)
mainid = fixerrors(mainid)
eid = 'No ID in system' if eid is None else str(eid)
x = mye.getkey() + ' ' + eid + ' ' + str(
syname) + ' ' + str(mainid)
except:
print 'Error processing string:', repr(eid)
continue
mainid = fixerrors(mainid)
n = x if x is not None else names[rownum] + ' ' + str(mainid)
# name match - if mismatch - store in error file
namegood = True
if employee is not None:
syname = fixname(employee.getname())
dbname = set(syname.split())
match = dbname.intersection((names[rownum]).split())
namegood = True if len(match) >= 2 else False
if namegood is False:
q = "File name: %s\nMicrogen name: %s\n\nIs this the same person?\n\n\t1 - Yes, this is the same person\n\t2 - No, this is NOT the same person\n\nResponse: " % (
str(names[rownum]), syname)
regex = "^1|2$"
error = "Invalid entry. Expecting a numeric value 1 or 2."
resp = getresponse(q, regex, error)
if int(resp) == 1:
namegood = True
n = employee.getkey() + ' ' + str(names[rownum]) + ' ' + str(
mainid) + ' ' + syname if not namegood else n
rep = {
'name': n,
'Tier 2 (5%)': 0,
'Pre-Employer Contributions': 0,
'Pre-Employee Contributions': 0,
'Post-Employer Contributions': 0,
'Post-Employee Contributions': 0,
'total': 0
}
for colnum in range(0, len(colnames)):
if colnames[colnum] in ml:
try:
rep[colnames[colnum]] += sh.cell_value(rownum, colnum)
rep['total'] += sh.cell_value(rownum, colnum)
except:
print "\nFailed processing data in cell " + str(
rownum) + ", " + str(colnum) + ": " + str(
getCell(sh, rownum, colnum))
sys.exit(2)
if not namegood:
report_error.append(rep)
else:
if new == 1:
report_new.append(rep)
else:
if employee.getregstatus() == 'Closed':
report_closed.append(rep)
else:
report.append(rep)
# generate reports: first for existing clients and then for new ones
tr = GetLastTR()
cid = company.getid()
if len(report) > 0:
tr = GenerateReports(report, schemes, mode, date, colnames, tr, 0, cid)
if len(report_new) > 0:
rn = report_new
tr = GenerateReports(rn, schemes, mode, date, colnames, tr, 1, cid)
if len(report_closed) > 0:
rc = report_closed
tr = GenerateReports(rc, schemes, mode, date, colnames, tr, 2, cid)
if len(report_error) > 0:
re = report_error
tr = GenerateReports(re, schemes, mode, date, colnames, tr, 3, cid)
if level > 1:
nr = report + report_new + report_closed + report_error
tr = GenerateOwnerReport(nr, schemes, internal, holdercomp, shareclass,
mode, date, colnames, tr, level, cid)
SaveLastTR(tr)
