def getData(social):
cn = sniCn()
cursor = cn.cursor()
colNames, colTypes = getColHeads('snimonthlypay', cursor, lower=True)
sqlgetData = 'select ' + ','.join(colNames) + ' from snimonthlypay where mssn = ? order by mbegindate asc'
data = cursor.execute(sqlgetData,social).fetchall()
return data, colNames
def __init__(self, parent):
wx.Panel.__init__(self, parent)
size = wx.GetDisplaySize()
self.maxsize = (size[0] * 0.9, size[1] * 0.8)
# Set up the buttons
btnSize = (130, 25)
cbSize = (150, 20)
cn = sniCn()
cursor = cn.cursor()
self.fileName = None
colNames, colTypes = getColHeads("snimonthlypay", cursor, lower=True)
self.btnChooseInFile = wx.Button(self, -1, "Choose file to load", size=btnSize)
self.Bind(wx.EVT_BUTTON, self.onClickChooseInFile, self.btnChooseInFile)
self.btnLoadFile = wx.Button(self, -1, "Load file", size=btnSize)
self.Bind(wx.EVT_BUTTON, self.onClickLoadDBFile, self.btnLoadFile)
self.btnShowChanges = wx.Button(self, -1, "ShowChanges", size=btnSize)
self.Bind(wx.EVT_BUTTON, self.onClickShowChanges, self.btnShowChanges)
self.btnShowData = wx.Button(self, -1, "Show Data", size=btnSize)
self.Bind(wx.EVT_BUTTON, self.onClickShowData, self.btnShowData)
socialtxt = wx.StaticText(self, -1, "Enter social", size=btnSize)
self.socialEntered = wx.TextCtrl(self, -1, "", size=btnSize)
self.chkXLFormat = wx.CheckBox(self, -1, "Save file in Excel format", size=btnSize)
self.chkDBFormat = wx.CheckBox(self, -1, "Save file to Database", size=btnSize)
self.chkXLFormat.SetValue(True)
self.chkDBFormat.SetValue(True)
self.txtLoadFile = wx.StaticText(self, -1, "")
ffBox = wx.StaticBox(self, -1, "Load monthly file")
dbBox = wx.StaticBox(self, -1, "Inquiry about social")
colBoxload = wx.StaticBox(self, -1, "Select columns to load")
colBoxinq = wx.StaticBox(self, -1, "Select columns to compare")
ffBoxSizer = wx.StaticBoxSizer(ffBox, wx.VERTICAL)
inqBoxSizer = wx.StaticBoxSizer(dbBox, wx.VERTICAL)
colBoxloadsizer = wx.StaticBoxSizer(colBoxload, wx.VERTICAL)
colBoxinqsizer = wx.StaticBoxSizer(colBoxinq, wx.VERTICAL)
self.loadChecks = OrderedDict(
zip(colNames, [wx.CheckBox(self, -1, name[1:], size=cbSize) for name in colNames])
)
self.inqChecks = OrderedDict(zip(colNames, [wx.CheckBox(self, -1, name[1:], size=cbSize) for name in colNames]))
for checks in [self.loadChecks, self.inqChecks]:
for check in checks.values():
check.SetValue(True) # start by setting them all on. Then edit a few below.
for check in [
self.loadChecks["mbegindate"],
self.loadChecks["menddate"],
self.inqChecks["mbegindate"],
self.inqChecks["menddate"],
self.loadChecks["mssn"],
self.inqChecks["mssn"],
]:
check.Enable(False)
for check in [
self.inqChecks["mearns"],
self.inqChecks["mbonusearns"],
self.inqChecks["mhours"],
self.inqChecks["mbegindate"],
self.inqChecks["menddate"],
self.inqChecks["mssn"],
]:
check.SetValue(False)
# lay out the screen
# load panels first
h1 = wx.BoxSizer(wx.HORIZONTAL)
for thing in [self.btnChooseInFile, self.chkXLFormat, self.chkDBFormat, self.btnLoadFile]:
h1.Add(thing)
h1.Add((5, 5))
for chk in self.loadChecks.values():
colBoxloadsizer.Add(chk)
for thing in [h1, self.txtLoadFile, colBoxloadsizer]:
ffBoxSizer.Add(thing, 0, wx.ALL, 2)
# query panel now
h2 = wx.BoxSizer(wx.HORIZONTAL)
for thing in [socialtxt, self.socialEntered, self.btnShowChanges, self.btnShowData]:
h2.Add(thing)
h2.Add((5, 5))
for chk in self.inqChecks.values():
colBoxinqsizer.Add(chk)
for thing in [h2, colBoxinqsizer]:
inqBoxSizer.Add(thing, 0, wx.ALL, 2)
h3 = wx.BoxSizer(wx.HORIZONTAL)
for thing in [ffBoxSizer, inqBoxSizer]:
# h3.Add((20,20),1)
h3.Add((5, 5))
h3.Add(thing)
v1 = wx.BoxSizer(wx.VERTICAL)
v1.Add((10, 10))
v1.Add(h3)
self.SetSizerAndFit(v1)
def processFile(fn, writeXL, writeDB):
XLmsg, DBmsg = '',''
inf = open(fn,'r') #Process the header record, write an error and exit if bad
flines = inf.readlines()
hLine=flines[0]
hfields='recid filename begindate enddate clientname filler'.split()
places = [1,7,27,35,43,53,600]
starts = places[:-1]
ends=places[1:]
headRec = namedtuple('HR', hfields)
dat = [ hLine[i-1:j-1] for i,j in zip(starts,ends)]
H = headRec(*dat)
goOn = False
msg = ''
if (H.filename.strip() == 'EDS PENSION MONTHLY') and \
(H.clientname.strip() == 'SCRIPPSNET') :
goOn = True
else:
msg = 'Bad header data, should be EDS PENSION MONTHLY SCRIPPSNET, I got '+ H.filename + H.clientname
"""
Process the main file
all fields have prefix of 'm' as monthly fields (!)
"""
mfields='SSN Employeeid Salutation Firstname Midinit Lastname Gender Birthdate Orghiredate \
Rechiredate filler Address1 Address2 Address3 City State Zip Country Homephonenum \
filler Locationdate Payrollloccode Pencompcode Russcompcode Unioncode filler \
Empstatusdate Payrollstatuscode Payrollactreacode Payrollreasoncode filler Earns Bonusearns \
Hours filler Salratedate Salannual Salschedhrs Saltargetbonus Suffix filler'.split()
mfields = ['m'+i.lower() for i in mfields]
places = [1,10,21,26,56,61,91,94,102,110,118,138,173,208,243,273,279,289,295,319,339,347,\
352,355,358,362,381,389,395,407,419,439,454,469,477,494,502,513,520,525,528,544]
starts=places[:-1]
ends=places[1:]
rows=[] # this stores all the results
fillerCols = [colNo for colNo, col in enumerate(mfields) if col == 'mfiller']
fillerCols.reverse() # we are going to pop these, so pop the biggest ones first or you will mess up the indexes
for i in fillerCols: mfields.pop(i)
# now add some extra fields for messages and the begin end dates
appendData = [H.begindate, H.enddate]
mfields.extend(['mbegindate','menddate'])
dateCols=[colNo for colNo, col in enumerate(mfields) if 'date' in col]
div100Cols = ['mearns','mbonusearns','msalannual','msalschedhrs','msaltargetbonus','mhours'] # div100Cols are the numeric columns that have to be divided by 100 - these are money
div100Cols = [mfields.index(i) for i in div100Cols]
div1Cols = ['msalschedhrs','msaltargetbonus','mhours'] #div1Cols are the numeric columns that are strings to be converted to integers - not divided by anything
div1Cols = [mfields.index(i) for i in div1Cols]
moneyCols = ['mearns', 'msalannual', 'mbonusearns']
moneyCols = [mfields.index(i) for i in moneyCols]
for L in flines[1:-1]: #make this [1:12] to just test the first 10
dat = [L[i-1:j-1] for i,j in zip(starts,ends)]
dat = [i.strip() for i in dat] # just take off any leading or trailing blanks
dat[0] = dat[0][:3] + '-' + dat[0][3:5] + '-' + dat[0][5:] # make a social out of the first field - social comes in as an integer
for i in fillerCols: dat.pop(i) # throw out the fillers
dat.extend(appendData)
for col in dateCols:
if dat[col]:
dat[col] = dt.strptime(dat[col], FFdateFormat)
for col in div100Cols:
dat[col] = (Decimal(dat[col])/100)
for col in div1Cols:
dat[col] = (int(dat[col]))
R = dict(zip(mfields, dat))
rows.append(R)
# now write the spreadsheet out
if writeXL:
outfn = fn[:-3]+'xls'
wb = xlwt.Workbook()
ws = wb.add_sheet('MonthlyData')
dateStyle = xlwt.easyxf(num_format_str='MM/DD/YYYY')
currStyle = xlwt.easyxf(num_format_str = '$#,##0.00')
genStyle = xlwt.easyxf(num_format_str = 'general')
numStyle = xlwt.easyxf(num_format_str="#,###.00")
intStyle = xlwt.easyxf(num_format_str="#,###")
colStyles, colFlds = [], []
for colNum, fld in enumerate(mfields): # write the col headings, prepare list of column styles
ws.write(0,colNum,fld)
with Timer() as t:
for rowNum, row in enumerate(rows):
dataRow = [row[k] for k in mfields]
for colNum, col in enumerate(dataRow):
if colNum in dateCols:
ws.write(rowNum+1,colNum,col,dateStyle)
elif colNum in div100Cols:
if colNum in moneyCols: # then it is a decimal and money too
ws.write(rowNum+1,colNum,col, currStyle)
else: # it is just a decimal
ws.write(rowNum+1,colNum,col, numStyle)
elif colNum in div1Cols:
ws.write(rowNum+1,colNum,col, intStyle)
else:
ws.write(rowNum+1,colNum,col, genStyle)
try:
wb.save(outfn)
result = rowNum + 1 #rownum starts from zero
except:
result = 0
msg = 'Could not save spreadsheet '+outfn+'.\nPlease check the filename is good and the sheet is not open.\n'+str(result)+' rows processed.'
print msg
print('ssWrite took %.03f sec.' % t.interval)
XLmsg = 'Spreadsheet ' + outfn + ' written. ' + str(result) + ' rows added.\n'
if result == 0:
Xlmsg = 'Error! ' + XLmsg
print XLmsg
# now write the databsae
if writeDB:
cn = sniCn()
cursor = cn.cursor()
outMsgs = fn[:-4]+'Msg.txt'
msgF = open(outMsgs,'w')
keyCols = ['mbegindate','mssn']
# sql to insert into snimonthlypay
sqlIns = "insert into sniMonthlypay (" + ",".join(mfields) + ") values ( " + ','.join('? ' for i in mfields) + ")"
# sql to updaqte snimonthlypay
sqlUpd = "update sniMonthlypay set "
updfields = [i for i in mfields if i not in keyCols]
sqlUpd2 = " = ?,".join(updfields) + '=? where '
sqlUpd3 = '=? and '.join(keyCols) + '=?'
sqlUpd = sqlUpd + sqlUpd2 + sqlUpd3
# sql to check for existing value - ie whether to do update or insert
sqlCheck = 'select mbegindate from snimonthlypay where mbegindate = ? and mssn = ?'
#messages and counters
dbErrors = []
rowsUpd, rowsIns = 0,0
for row in rows:
# check if this date and ssn thing already in
keyVals = [row[k] for k in keyCols]
dataInDB = cursor.execute(sqlCheck, keyVals).fetchall()
if dataInDB: #then to an update
updRow = [row[k] for k in updfields]
try:
cursor.execute(sqlUpd,updRow + keyVals)
except pyodbc.Error as er: # something went wrong
errNo, errData = er
errStrin = 'Unexpected update error-->'+str(errNo)+'\n'+str(errData)+'\n'
dbErrors.append(errStrin)
else:
rowsUpd += 1
msgF.write(sqlUpd+'\n'+str(updRow)+'\n'+str(keyVals)+'\n')
else: # do an insert
dataRow = [row[k] for k in mfields]
try:
cursor.execute(sqlIns,dataRow)
except pyodbc.Error as er:
errNo, errData = er
errStrin = 'Unexpected update error-->'+str(errNo)+'\n'+str(errData)+'\n'
dbErrors.append(errStrin)
else:
rowsIns += 1
msgF.write(sqlIns+'\n'+str(dataRow)+'\n')
numErrors = len(dbErrors)
DBmsg = 'Database table sniMonthlyPay written. ' + str(rowsUpd) + ' rows updated, ' + str(rowsIns) + ' rows inserted, ' + str(numErrors) + ' errors.\n'
# need to backout if any errors, and return error messages...
if numErrors > 0:
cn.rollback()
DBmsg += 'Errors reported, so database records rolled back. First 10 errors are:'
for i in range(10):
DBmsg += dbErrors[i]
else:
cn.commit()
msgF.close()
return (XLmsg+DBmsg)