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)