def getdatabase():
q = "Select database to connect to:\n\n\t1 - Production (Petra5)\n\t2 - Test (Petra5_Test)\n\nDatabase: "
regex = "^1|2$"
error = "Invalid entry. Expecting a 1 or 2"
dbase = getresponse(q,regex,error)
try:
po.database = 'Petra5' if int(dbase)==1 else 'Petra5_Test'
po.testconnection()
except:
print "\nError: Cannot connect to database"
getdatabase()
return int(dbase)
def getdatabase():
q = "Select database to connect to:\n\n\t1 - Production (Petra5)\n\t2 - Test (Petra5_Test)\n\nDatabase: "
regex = "^1|2$"
error = "Invalid entry. Expecting a 1 or 2"
dbase = getresponse(q, regex, error)
try:
po.database = 'Petra5' if int(dbase) == 1 else 'Petra5_Test'
po.testconnection()
except:
print "\nError: Cannot connect to database"
getdatabase()
return int(dbase)
def IdentifyCompany(filename):
print "\n\nIdentifying the company\n"
print "-----------------------"
po.companies = po.set_companies()
company = None
f = re.sub(r"(data\\)?\d+\s*\-\s*\w{3}\s*\-\s*(.*?)\b", '', filename)
f = re.sub(r'\.\w+', '', f)
#l = str(f).split()
pattern = "(.*?)" + str(f) + "(.*?)"
for c in po.companies:
m = re.match(pattern, c.getname(), re.IGNORECASE)
if m is not None:
company = c
if company == None:
company = CompanyOptions()
else:
resp = None
while not resp:
resp = raw_input("\nFound company '" + company.getname() +
"'. Is this correct [y/n]: ")
if not re.match("y|n", resp):
print "Invalid entry. Expecting a 'y' or 'n'"
resp = None
if re.match("no?", resp):
company = CompanyOptions()
return company
def IdentifyCompany(filename):
print "\n\nIdentifying the company\n"
print "-----------------------"
po.companies = po.set_companies()
company = None
f = re.sub(r"(data\\)?\d+\s*\-\s*\w{3}\s*\-\s*(.*?)\b",'',filename)
f = re.sub(r'\.\w+','',f)
#l = str(f).split()
pattern = "(.*?)"+str(f)+"(.*?)"
for c in po.companies:
m = re.match(pattern,c.getname(),re.IGNORECASE)
if m is not None:
company = c
if company == None:
company = CompanyOptions()
else:
resp = None
while not resp:
resp = raw_input("\nFound company '"+company.getname()+"'. Is this correct [y/n]: ")
if not re.match("y|n", resp):
print "Invalid entry. Expecting a 'y' or 'n'"
resp = None
if re.match("no?", resp):
company = CompanyOptions()
return company
def GenerateOwnerReport(report, schemes, internal, holdercomp, shareclass,
omode, date, colnames, trnum, level, cid):
tr = trnum
ifhc = None
ifc = None
ifid = None
scc = None
schc = None
scid = None
total_payments = 0
for scheme in schemes:
valcol = getNumColName(scheme.getname())
units = 0
payment = 0
if valcol is not None and valcol in colnames:
scc, schc, ifhc, ifc, scid, ifid = po.getOwnerFundCode(
scheme, level, internal, holdercomp, shareclass)
if level == 3:
price = getprice(scid, date, omode)
for r in report:
units = units + (r[valcol] / float(price))
payment = payment + r[valcol]
total_payments += payment
tr = WriteToFile('share', scc, schc, units, price, payment,
date, tr, cid)
else:
price = getprice(ifid, date, omode)
for r in report:
units = units + (r[valcol] / float(price))
payment = payment + r[valcol]
tr = WriteToFile('internal', ifc, ifhc, units, price, payment,
date, tr, cid)
# fill out internal fund for only level 3
if level == 3:
price = getprice(ifid, date, omode)
units = total_payments / float(price)
tr = WriteToFile('internal', ifc, ifhc, units, price, total_payments,
date, tr, cid)
print "\nGenerated internal and share files."
return tr
def GenerateOwnerReport(report,schemes,internal,holdercomp,shareclass,omode,date,colnames,trnum,level,cid):
tr = trnum
ifhc = None
ifc = None
ifid = None
scc = None
schc = None
scid = None
total_payments = 0
for scheme in schemes:
valcol = getNumColName(scheme.getname())
units = 0
payment = 0
if valcol is not None and valcol in colnames:
scc, schc, ifhc, ifc, scid, ifid = po.getOwnerFundCode(scheme, level, internal,holdercomp,shareclass)
if level == 3:
price = getprice(scid, date, omode)
for r in report:
units = units + (r[valcol] / float(price))
payment = payment + r[valcol]
total_payments += payment
tr = WriteToFile('share',scc,schc,units,price,payment,date,tr,cid)
else:
price = getprice(ifid, date, omode)
for r in report:
units = units + (r[valcol] / float(price))
payment = payment + r[valcol]
tr=WriteToFile('internal',ifc,ifhc,units,price,payment,date,tr,cid)
# fill out internal fund for only level 3
if level == 3:
price = getprice(ifid, date, omode)
units = total_payments / float(price)
tr = WriteToFile('internal',ifc,ifhc,units,price,total_payments,date,tr,cid)
print "\nGenerated internal and share files."
return tr
def getprice(scheme, date, mode):
p = None
price = None
if int(mode) == 2:
price = 1.000000
else:
if scheme is not None:
price = po.getPrice(scheme, date)
if price is None:
nf = "Cannot find price for given date. "
q = "Please enter price: "
q = nf + q if scheme is not None else ""
regex = "^\d+(\.\d+)?$"
error = "Invalid entry. Expecting a numeric value."
price = getresponse(q, regex, error)
price = round(float(price), 6)
return price
def getprice(scheme, date, mode):
p = None
price = None
if int(mode)==2:
price = 1.000000
else:
if scheme is not None:
price = po.getPrice(scheme, date)
if price is None:
nf = "Cannot find price for given date. "
q = "Please enter price: "
q = nf+q if scheme is not None else ""
regex = "^\d+(\.\d+)?$"
error = "Invalid entry. Expecting a numeric value."
price = getresponse(q,regex,error)
price = round(float(price),6)
return price
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
mailServer.ehlo()
mailServer.starttls()
mailServer.ehlo()
mailServer.login(gmail_user, gmail_pwd)
mailServer.sendmail(gmail_user, to, msg.as_string())
# Should be mailServer.quit(), but that crashes...
mailServer.close()
error = []
try:
# Update employee and employers lists
po.UpdateClients()
except:
error.append('Problems with updating clients.')
try:
# Update funds information
po.UpdateFunds()
except:
error.append('Problems with updating funds.')
try:
# Update deals information
po.UpdateDeals()
except:
error.append('Problems with updating deals.')
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)
part.add_header('Content-Disposition',
'attachment; filename="%s"' % os.path.basename(attach))
msg.attach(part)
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
mailServer.ehlo()
mailServer.starttls()
mailServer.ehlo()
mailServer.login(gmail_user, gmail_pwd)
mailServer.sendmail(gmail_user, to, msg.as_string())
# Should be mailServer.quit(), but that crashes...
mailServer.close()
error = []
try:
# Update employee and employers lists
po.UpdateClients()
except:
error.append('Problems with updating clients.')
if len(error):
errors = "\n".join(error)
now = datetime.datetime.now()
mail(
"*****@*****.**", "[Web Updater] Error running update on " +
now.strftime("%Y-%m-%d %H:%M"),
"The web updater script failed to run successfully. The following problems occured:\n\n"
+ errors)
