def getFromDB(self, name):
self.d = db2.execute(
"SELECT c.id, c.name AS cagename, c.cagegroup, c.expectingpl, c.notes, c.date_activated, c.date_inactivated, c.location, c.active, m.name, housing.currentcage, housing.start_date, housing.end_date, lab_members.name AS caretaker FROM cages as c LEFT JOIN housing ON housing.cage_id=c.id LEFT JOIN mice as m ON m.id=housing.mouse_id LEFT JOIN care_taker AS ct ON ct.cage_id=c.id LEFT JOIN lab_members ON ct.lab_member_id=lab_members.id WHERE c.name=%s",
(name, ))
if self.d == []:
return "This cage does not exist"
self.d = self.d[0]
for key in self.d:
if self.d[key] is None:
self.d[key] = ''
elif self.d[key] == 1 and key != 'id':
self.d[key] = 'Yes'
elif self.d[key] == 0:
self.d[key] = 'No'
if self.d['date_inactivated'] != '':
self.d['date_inactivated'] = date2str(self.d['date_inactivated'])
if self.d['date_activated'] != '':
self.d['date_activated'] = date2str(self.d['date_activated'])
self.mice = db2.execute(
"SELECT h.mouse_id, m.name, m.id, m.sex, IF(EXISTS(SELECT * from experiments WHERE mouse_id=m.id), 'reserved','notreserved') AS reserved FROM housing AS h INNER JOIN mice AS m ON h.mouse_id=m.id WHERE h.cage_id=%s AND h.currentcage=1",
(self.d['id'], ))
self.litters = db2.execute(
"SELECT DOB, mother_id FROM litters WHERE litters.cage_id=%s",
(self.d['id'], ))
def getFromDB(self,mouseID):
self.d=db2.execute("SELECT m.id, m.name AS mousename, m.strain, m.sex, m.life_status, m.breeding_status, m.DOB, m.DOD, m.cause_of_death, m.tag, IF(EXISTS(SELECT * from experiments WHERE mouse_id=m.id), 'reserved','notreserved') AS reserved, m.notes AS mouse_notes, m.genotyped, cages.name AS cagename, mom.name AS mother, dad.name AS father, lab_members.name AS reserve_lab_member, experiments.date AS reserve_date, experiments.description AS reserve_description FROM mice AS m LEFT JOIN housing ON housing.mouse_id=m.id LEFT JOIN cages ON cages.id=housing.cage_id LEFT JOIN lineage ON lineage.child_id=m.id LEFT JOIN mice AS mom ON lineage.mother_id=mom.id LEFT JOIN mice AS dad ON lineage.father_id=dad.id LEFT JOIN experiments ON experiments.mouse_id=m.id LEFT JOIN lab_members ON experiments.lab_member_id=lab_members.id LEFT JOIN genetics ON genetics.mouse_id=m.id WHERE m.id=%s ",(mouseID,))
if self.d ==[]:
return "This mouse does not exist"
self.d=self.d[0]
self.d['experiments']=db.execute("SELECT lab_members.name AS reserve_lab_member, mr.date AS reserve_date, mr.description AS reserve_description, mr.notes AS reserve_notes, mr.status AS reserve_status, mr.filenames AS reserve_filenames FROM experiments AS mr LEFT JOIN lab_members ON mr.lab_member_id=lab_members.id WHERE mr.mouse_id=%s ORDER BY reserve_date ASC",(self.d['id'],))
for i in range(len(self.d['experiments'])):
self.d['experiments'][i]=list(self.d['experiments'][i])
self.d['experiments'][i][1]=date2str(self.d['experiments'][i][1])
self.d['experiments'].append(['','','','','',''])
# e.g. self.d['genes']=[('PV-Cre', '+-'), ('i-tdTomato', '++')]
self.d['genes']=db.execute("SELECT genes.name, zygosity FROM genetics LEFT JOIN genes ON genes.id=genetics.gene_id WHERE genetics.mouse_id=%s",(self.d['id'],))
self.d['genes'].append(['',''])
cages=db.execute("SELECT cages.name, h.start_date, h.end_date, h.currentcage FROM housing AS h LEFT JOIN cages ON h.cage_id=cages.id WHERE h.mouse_id=%s ORDER BY h.start_date DESC",(self.d['id'],))
cages.append(tuple(['','','','']))
for key in self.d:
if self.d[key] is None:
self.d[key]=''
elif self.d[key]==1 and key!='id':
self.d[key]='Yes'
elif self.d[key]==0:
self.d[key]='No'
if self.d['DOB'] != '': self.d['DOB']=date2str(self.d['DOB']);
if self.d['DOD'] != '': self.d['DOD']=date2str(self.d['DOD']);
if self.d['reserve_date'] != '': self.d['reserve_date']=date2str(self.d['reserve_date']);
self.d['oldcages']=[]
self.d['currentcage']=['','']
for cage in cages:
if cage[3]==1:
self.d['currentcage']=[cage[0],date2str(cage[1])]
elif cage[1] != '' and cage[2] !='':
self.d['oldcages'].append([cage[0],date2str(cage[1]),date2str(cage[2])])
self.d['oldcages'].append(['','',''])
def getFromDB(self,name):
self.d=db2.execute("SELECT c.id, c.name AS cagename, c.cagegroup, c.expectingpl, c.notes, c.date_activated, c.date_inactivated, c.location, c.active, m.name, housing.currentcage, housing.start_date, housing.end_date, lab_members.name AS caretaker FROM cages as c LEFT JOIN housing ON housing.cage_id=c.id LEFT JOIN mice as m ON m.id=housing.mouse_id LEFT JOIN care_taker AS ct ON ct.cage_id=c.id LEFT JOIN lab_members ON ct.lab_member_id=lab_members.id WHERE c.name=%s",(name,))
if self.d ==[]:
return "This cage does not exist"
self.d=self.d[0]
for key in self.d:
if self.d[key] is None:
self.d[key]=''
elif self.d[key]==1 and key!='id':
self.d[key]='Yes'
elif self.d[key]==0:
self.d[key]='No'
if self.d['date_inactivated'] != '': self.d['date_inactivated']=date2str(self.d['date_inactivated']);
if self.d['date_activated'] != '': self.d['date_activated']=date2str(self.d['date_activated']);
self.mice=db2.execute("SELECT h.mouse_id, m.name, m.id, m.sex, IF(EXISTS(SELECT * from experiments WHERE mouse_id=m.id), 'reserved','notreserved') AS reserved FROM housing AS h INNER JOIN mice AS m ON h.mouse_id=m.id WHERE h.cage_id=%s AND h.currentcage=1",(self.d['id'],))
self.litters=db2.execute("SELECT DOB, mother_id FROM litters WHERE litters.cage_id=%s",(self.d['id'],))
def refresh(self):
username = checkPassword()
if not username:
return """<meta http-equiv="refresh" content="0;url=/home/login" />""" #This takes an unknown user to the login screen
article = "<thead><tr>"
#########################
######### CREATE HEADER
columns = [
'mousename', 'life_status', 'DOB', 'DOD', 'mouse_notes',
'reserve_date', 'reserve_description', 'reserve_lab_member'
]
for h in columns:
article += "<th data-header='{0}' filter class='header'>{1}".format(
h, getPrettyText(h))
article += "<img src='/support/images/drag-handle.png' class='col-handle'></th>"
article += "</tr></thead><tbody>"
query = """SELECT m.name AS mousename, m.DOB, m.DOD, m.life_status, m.notes AS mouse_notes,
lab_members.name AS reserve_lab_member, mice_reservations.date AS reserve_date, mice_reservations.description AS reserve_description
FROM mice_reservations
LEFT JOIN lab_members ON mice_reservations.lab_member_id=lab_members.id
LEFT JOIN mice AS m ON m.id=mice_reservations.mouse_id"""
answer = db2.execute(query)
if answer != []:
for entry in answer:
article += "<tr>"
for col in columns:
if entry[col] == None:
entry[col] = '-'
elif type(entry[col]) == type(datetime.datetime.now()):
entry[col] = date2str(entry[col])
elif entry[col] == 0:
entry[col] = 'No'
elif entry[col] == 1:
entry[col] = 'Yes'
if col == 'mouse_notes':
text = html_escape(entry[col])
article += "<td class='{0} tooltip' title='{1}'>{2}</td>".format(
col, text, text[:20]
) #only display the first 20 characters, tooltip the rest
else:
article += "<td class='{0}'>{1}</td>".format(
col, entry[col])
article += "</tr>"
article += "</tbody>"
return article
def refresh(self):
username=checkPassword()
if not username:
return """<meta http-equiv="refresh" content="0;url=/home/login" />""" #This takes an unknown user to the login screen
article="<thead><tr>"
#########################
######### CREATE HEADER
columns=['mousename','life_status','DOB','DOD','mouse_notes','reserve_date','reserve_description','reserve_lab_member']
for h in columns:
article+="<th data-header='{0}' filter class='header'>{1}".format(h,getPrettyText(h))
article+="<img src='/support/images/drag-handle.png' class='col-handle'></th>"
article+="</tr></thead><tbody>"
query= """SELECT m.name AS mousename, m.DOB, m.DOD, m.life_status, m.notes AS mouse_notes,
lab_members.name AS reserve_lab_member, mice_reservations.date AS reserve_date, mice_reservations.description AS reserve_description
FROM mice_reservations
LEFT JOIN lab_members ON mice_reservations.lab_member_id=lab_members.id
LEFT JOIN mice AS m ON m.id=mice_reservations.mouse_id"""
answer=db2.execute(query)
if answer!=[]:
for entry in answer:
article+="<tr>"
for col in columns:
if entry[col]==None:
entry[col]='-'
elif type(entry[col])==type(datetime.datetime.now()):
entry[col]=date2str(entry[col])
elif entry[col]==0:
entry[col]='No'
elif entry[col]==1:
entry[col]='Yes'
if col=='mouse_notes':
text=html_escape(entry[col])
article+="<td class='{0} tooltip' title='{1}'>{2}</td>".format(col,text,text[:20]) #only display the first 20 characters, tooltip the rest
else:
article+="<td class='{0}'>{1}</td>".format(col,entry[col])
article+="</tr>"
article+="</tbody>"
return article