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 reporthtml(self):
clientcols = getColHeads('clients', cursor)[0]
sql_client = 'select ' + ','.join(clientcols) + ' from clients'
cursor.execute(sql_client)
clients = cursor.fetchall()
year = str(date.today().year)
project = year + ' Actuarial Tasks'
taskcols = getColHeads('tasks', cursor)[0]
sql_tasks = "select " + ",".join(taskcols) + " from tasks where tproject = '{0}'".format(project)
tasks = cursor.execute(sql_tasks).fetchall()
# now we have the raw data let's make the tasks and data stores
task_data = OrderedDict()
for cli in clients:
task_data[cli.clientcode] = OrderedDict(zip(clientcols,cli))
tskDict = {}
for t in tasks:
tsk = Task(cc=None,user=None,tclient=t.tclient,tproject=t.tproject,taskdata=t)
tskDict[(t.tclient,t.tdescription)] = tsk
task_order = ['8955-SSA','FundVal or Alloc','Acct Val BoY','Acct Val EoY','STMT','PBGC','5500','SAR/AFN','SB']
output = HTML.Table(header_row = clientcols + task_order)
for cli in clients:
row = list(cli)
for task in task_order:
if (cli.clientcode,task) in tskDict:
t = tskDict[(cli.clientcode, task)]
colour = t.colour()[0]
coloured_cell = HTML.TableCell(t.duedateout(), bgcolor = colour)
row.append(coloured_cell)
else:
row.append('No data')
output.rows.append(row)
keys = t.return_constants()
k = HTML.Table(header_row=['Key'])
for key in keys:
cell = HTML.TableCell(key[1],bgcolor=key[0])
k.rows.append([cell])
return str(k) + str(output)
def __init__(self, parent, IBPanel):
self.parent = parent
self.IBPanel = IBPanel
wx.Panel.__init__(self, self.parent, -1)
# set up the cursor to get at the to do lists
self.td_conn = pyodbc.connect(ConnString(11))
self.td_cur = self.td_conn.cursor()
self.td_cols, self.td_typs = getColHeads("vw_all", self.td_cur)
self.projectcol_ix = self.td_cols.index("tProject")
self.taskcol_ix = self.td_cols.index("tTaskid")
self.clientcol_ix = self.td_cols.index("tClient")
self.statuscol_ix = self.td_cols.index("tStatus")
self.responsiblecol_ix = self.td_cols.index("tResponsible")
self.expectedcol_ix = self.td_cols.index("tExpected")
self.progresscol_ix = self.td_cols.index("tProgress")
self.user = os.environ["USERNAME"]
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)