コード例 #1
0
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
コード例 #2
0
ファイル: SNIPage.py プロジェクト: philipjellis/Warehouse
 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)
コード例 #3
0
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)