def changeemail(self, fields):
time.sleep(1)
user = checkPassword()
if not user:
return """<meta http-equiv="refresh" content="0;url=/home/login" />"""
d = {
urllib.parse.unquote(i.split('=')[0]):
urllib.parse.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
newemail1 = d['newemail1']
newemail2 = d['newemail2']
password = d['password']
if newemail1 == '' or newemail2 == '':
return 'You need to fill in all the required email data'
if newemail1 != newemail2:
return "You entered your new email incorrectly. Please try again."
password = hashlib.md5((password + salt).encode('utf-8')).hexdigest()
if not DI.isUser(user, password):
return "You entered your password incorrectly."
db.execute('''UPDATE lab_members set email=%s WHERE name=%s ''',
(newemail1, user))
return 'Your email has been updated to <b>{0}</b>'.format(newemail1)
def changepassword(self, fields):
time.sleep(1)
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
oldpassword=d['oldpassword']
newpassword1=d['newpassword1']
newpassword2=d['newpassword2']
user=checkPassword()
if not user:
return """You have been logged out. <a href="/home/login">Log in</a>."""
if oldpassword=='' or newpassword1=='' or newpassword2=='':
return 'You need to fill in all the required password data'
if newpassword1!=newpassword2:
return "You entered your new password incorrectly. Please try again."
elif len(newpassword1)<=5:
return "<p>Your password needs to be greater than 5 characters</p>"
oldpass=hashlib.md5((oldpassword+salt).encode('utf-8')).hexdigest()
newpass=hashlib.md5((newpassword1+salt).encode('utf-8')).hexdigest()
## RESET COOKIE
cookie = cherrypy.response.cookie
sida=user
sidb=newpass
cookie['sida']=sida
cookie['sida']['expires'] = 12 * 30 * 24 * 60 * 60
cookie['sida']["path"] = "/"
cookie['sidb']=sidb
cookie['sidb']['expires'] = 12 * 30 * 24 * 60 * 60
cookie['sidb']["path"] = "/"
cherrypy.request.cookie=cookie
data=db.execute("""SELECT id FROM lab_members WHERE name=%s AND password=%s""", (user,oldpass), commit=False)
if len(data)==0:
return "Your password is incorrect."
db.execute('''UPDATE lab_members SET password=%s WHERE name=%s''', (newpass, user))
return 'Your password has been updated!'
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 addstrain(self,fields):
if checkPassword()!='admin':
return "Only the admin can do this"
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
if db.execute('SELECT COUNT(*) FROM strains WHERE name=%s',(d['strainname'],))[0][0]>0:
return 'This strain has already been added to the database.'
db.execute('INSERT INTO strains SET name=%s',(d['strainname'],))
return "Successfully added '{0}' to the database".format(d['strainname'])
def removegene(self,fields):
if checkPassword()!='admin':
return "Only the admin can do this"
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
ntimesused=db.execute("""SELECT COUNT(*) FROM genetics LEFT JOIN genes ON genetics.gene_id=genes.id WHERE genes.name=%s""",(d['gene'],))[0][0]
if ntimesused>0:
return "Unable to remove '{0}' from the database because it is used {1} times.".format(d['gene'],ntimesused)
db.execute('DELETE FROM genes WHERE name=%s',(d['gene'],))
return "Sucessfully removed '{0}' from the database".format(d['gene'])
def removeuser(self,fields):
if checkPassword()!='admin':
return "Only the admin can do this"
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
if d['user']=='admin':
return "You cannot delete the user 'admin'"
nexperiments=db.execute("""SELECT COUNT(*) FROM lab_members INNER JOIN experiments AS mr ON lab_members.id=mr.lab_member_id WHERE lab_members.name=%s""",(d['user'],))[0][0]
ncages=db.execute("""SELECT COUNT(*) FROM lab_members INNER JOIN care_taker ON lab_members.id=care_taker.lab_member_id WHERE lab_members.name=%s""",(d['user'],))[0][0]
if ncages>0 or nexperiments>0:
return "Unable to remove '{0}' from the database because '{0}' has '{1}' experiments and '{2}' cages in the database".format(d['user'],nexperiments,ncages)
db.execute('DELETE FROM lab_members WHERE name=%s',(d['user'],))
return "Sucessfully removed '{0}' from the database".format(d['user'])
def addgene(self,fields):
if checkPassword()!='admin':
return "Only the admin can do this"
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
if d['genedefaultstatus']=='+':
d['genedefaultstatus']=True
elif d['genedefaultstatus']=='-':
d['genedefaultstatus']=False
if db.execute('SELECT COUNT(*) FROM genes WHERE name=%s',(d['genename'],))[0][0]>0:
return 'This gene has already been added to the database.'
db.execute('INSERT INTO genes SET name=%s, default_presence=%s',(d['genename'],d['genedefaultstatus']))
return "Successfully added '{0}' to the database".format(d['genename'])
def removestrain(self,fields):
if checkPassword()!='admin':
return "Only the admin can do this"
try:
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
except IndexError:
return "Server Error. The function removestrain() was passed variable '{0}'. Try refreshing the page.".format(fields)
ntimesused=db.execute("""SELECT COUNT(*) FROM mice WHERE strain=%s""",(d['strain'],))[0][0]
if ntimesused>0:
return "Unable to remove '{0}' from the database because it is used {1} times.".format(d['strain'],ntimesused)
db.execute('DELETE FROM strains WHERE name=%s',(d['strain'],))
return "Sucessfully removed '{0}' from the database".format(d['strain'])
def addstrain(self, fields):
if checkPassword() != 'admin':
return "Only the admin can do this"
d = {
urllib2.unquote(i.split('=')[0]): urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
if db.execute('SELECT COUNT(*) FROM strains WHERE name=%s',
(d['strainname'], ))[0][0] > 0:
return 'This strain has already been added to the database.'
db.execute('INSERT INTO strains SET name=%s', (d['strainname'], ))
return "Successfully added '{0}' to the database".format(
d['strainname'])
def removegene(self, fields):
if checkPassword() != 'admin':
return "Only the admin can do this"
d = {
urllib2.unquote(i.split('=')[0]): urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
ntimesused = db.execute(
"""SELECT COUNT(*) FROM genetics LEFT JOIN genes ON genetics.gene_id=genes.id WHERE genes.name=%s""",
(d['gene'], ))[0][0]
if ntimesused > 0:
return "Unable to remove '{0}' from the database because it is used {1} times.".format(
d['gene'], ntimesused)
db.execute('DELETE FROM genes WHERE name=%s', (d['gene'], ))
return "Sucessfully removed '{0}' from the database".format(d['gene'])
def adduser(self,fields):
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
name=d['newusername']
if name=='':
return 'You must enter a username'
if d['newpassword1']!=d['newpassword2']:
return "Your passwords don't match"
password=d['newpassword1']
if len(password)<5:
return 'Your password needs to be at least 5 characters long'
email=d['email']
hashedpassword=hashlib.md5((password+salt).encode('utf-8')).hexdigest()
#CHECK IF USER ALREADY EXISTS!!!
db.execute(""" INSERT INTO lab_members SET name=%s, password=%s, email=%s, viewtype='mouse', columns='mousename,,cagename,,cagename2,,genetics,,' """, (name, hashedpassword, email))
return "Successfully added '{}' to database".format(name)
def getAllColumns(table):
if table=='mice':
return ['id','name','strain','genotyped','sex','life_status','breeding_status','DOB','DOD','cause_of_death','tag','notes']
elif table=='genetics':
return ['mouse_id','gene_id','zygosity']
elif table=='lineage':
return ['mother_id','father_id','child_id']
elif table=='experiments':
return ['mouse_id','lab_member_id','date','description']
elif table=='care_taker':
return ['cage_id','lab_member_id']
elif table=='housing':
return ['mouse_id', 'cage_id', 'start_date', 'end_date']
elif table=='lab_members':
return ['id', 'name', 'password', 'email']
elif table=='genes':
return ['id', 'name', 'default_presence']
elif table=='cages':
return ['id', 'name', 'notes', 'active', 'date_activated', 'date_inactivated', 'location','expectingpl','cagegroup']
elif table=='transplants':
return ['donor_id', 'recipient_id', 'date', 'notes']
else:
columns=db.execute("SHOW COLUMNS FROM "+table)
col=[]
for c in columns:
col.append(c[0])
return col
def addgene(self, fields):
if checkPassword() != 'admin':
return "Only the admin can do this"
d = {
urllib2.unquote(i.split('=')[0]): urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
if d['genedefaultstatus'] == '+':
d['genedefaultstatus'] = True
elif d['genedefaultstatus'] == '-':
d['genedefaultstatus'] = False
if db.execute('SELECT COUNT(*) FROM genes WHERE name=%s',
(d['genename'], ))[0][0] > 0:
return 'This gene has already been added to the database.'
db.execute('INSERT INTO genes SET name=%s, default_presence=%s',
(d['genename'], d['genedefaultstatus']))
return "Successfully added '{0}' to the database".format(d['genename'])
def resetpassword(self, email):
names=DI.getusername(email)
if names is False:
return "That email address isn't associated with an account. Please enter a different email address."
subject='Password reset for Glams'
newpassword=''.join(choice(chars) for x in range(10))
if len(names)==1:
name=names[0]
text='Your user name is {} '.format(name)
text+=". Your new password is '{}'.".format(newpassword)
else:
text='The user names associated with this email address are: ' + ', '.join(names)
text+=". The new password for those accounts is '{}'.".format(newpassword)
html='<p>{}</p>'.format(text)
hashedpassword=hashlib.md5((newpassword+salt).encode('utf-8')).hexdigest()
db.execute('''UPDATE lab_members SET password=%s WHERE email=%s ''', (hashedpassword, email))
sendmail(email, subject, html, text)
return "Your password has been reset."
def refreshgenes(self):
article = ''
vals = db.execute("SELECT name FROM genes")
if vals == []:
article = '<option></option>'
else:
for val in vals:
article += '<option>{0}</option>'.format(val[0])
return article
def refreshgenes(self):
article=''
vals=db.execute("SELECT name FROM genes")
if vals==[]:
article='<option></option>'
else:
for val in vals:
article+='<option>{0}</option>'.format(val[0])
return article
def changepassword(self, fields):
time.sleep(1)
d = {
urllib.parse.unquote(i.split('=')[0]):
urllib.parse.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
oldpassword = d['oldpassword']
newpassword1 = d['newpassword1']
newpassword2 = d['newpassword2']
user = checkPassword()
if not user:
return """You have been logged out. <a href="/home/login">Log in</a>."""
if oldpassword == '' or newpassword1 == '' or newpassword2 == '':
return 'You need to fill in all the required password data'
if newpassword1 != newpassword2:
return "You entered your new password incorrectly. Please try again."
elif len(newpassword1) <= 5:
return "<p>Your password needs to be greater than 5 characters</p>"
oldpass = hashlib.md5((oldpassword + salt).encode('utf-8')).hexdigest()
newpass = hashlib.md5(
(newpassword1 + salt).encode('utf-8')).hexdigest()
## RESET COOKIE
cookie = cherrypy.response.cookie
sida = user
sidb = newpass
cookie['sida'] = sida
cookie['sida']['expires'] = 12 * 30 * 24 * 60 * 60
cookie['sida']["path"] = "/"
cookie['sidb'] = sidb
cookie['sidb']['expires'] = 12 * 30 * 24 * 60 * 60
cookie['sidb']["path"] = "/"
cherrypy.request.cookie = cookie
data = db.execute(
"""SELECT id FROM lab_members WHERE name=%s AND password=%s""",
(user, oldpass),
commit=False)
if len(data) == 0:
return "Your password is incorrect."
db.execute('''UPDATE lab_members SET password=%s WHERE name=%s''',
(newpass, user))
return 'Your password has been updated!'
def isUser(name, hashedpassword):
data=db.execute("""SELECT password FROM lab_members WHERE name=%s """, (name,), commit=False)
try:
data=data[0][0]
except IndexError:
return False
if data==hashedpassword:
return True
else:
return False
def changeemail(self, fields):
time.sleep(1)
user=checkPassword()
if not user:
return """<meta http-equiv="refresh" content="0;url=/home/login" />"""
d={urllib2.unquote(i.split('=')[0]):urllib2.unquote(i.split('=')[1]) for i in [tmp for tmp in fields.split('&')]}
newemail1=d['newemail1']
newemail2=d['newemail2']
password=d['password']
if newemail1=='' or newemail2=='':
return 'You need to fill in all the required email data'
if newemail1!=newemail2:
return "You entered your new email incorrectly. Please try again."
password=hashlib.md5((password+salt).encode('utf-8')).hexdigest()
if not DI.isUser(user, password):
return "You entered your password incorrectly."
db.execute('''UPDATE lab_members set email=%s WHERE name=%s ''', (newemail1, user))
return 'Your email has been updated to <b>{0}</b>'.format(newemail1)
def removeuser(self, fields):
if checkPassword() != 'admin':
return "Only the admin can do this"
d = {
urllib2.unquote(i.split('=')[0]): urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
if d['user'] == 'admin':
return "You cannot delete the user 'admin'"
nexperiments = db.execute(
"""SELECT COUNT(*) FROM lab_members INNER JOIN experiments AS mr ON lab_members.id=mr.lab_member_id WHERE lab_members.name=%s""",
(d['user'], ))[0][0]
ncages = db.execute(
"""SELECT COUNT(*) FROM lab_members INNER JOIN care_taker ON lab_members.id=care_taker.lab_member_id WHERE lab_members.name=%s""",
(d['user'], ))[0][0]
if ncages > 0 or nexperiments > 0:
return "Unable to remove '{0}' from the database because '{0}' has '{1}' experiments and '{2}' cages in the database".format(
d['user'], nexperiments, ncages)
db.execute('DELETE FROM lab_members WHERE name=%s', (d['user'], ))
return "Sucessfully removed '{0}' from the database".format(d['user'])
def removestrain(self, fields):
if checkPassword() != 'admin':
return "Only the admin can do this"
try:
d = {
urllib2.unquote(i.split('=')[0]):
urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
except IndexError:
return "Server Error. The function removestrain() was passed variable '{0}'. Try refreshing the page.".format(
fields)
ntimesused = db.execute(
"""SELECT COUNT(*) FROM mice WHERE strain=%s""",
(d['strain'], ))[0][0]
if ntimesused > 0:
return "Unable to remove '{0}' from the database because it is used {1} times.".format(
d['strain'], ntimesused)
db.execute('DELETE FROM strains WHERE name=%s', (d['strain'], ))
return "Sucessfully removed '{0}' from the database".format(
d['strain'])
def adduser(self, fields):
d = {
urllib2.unquote(i.split('=')[0]): urllib2.unquote(i.split('=')[1])
for i in [tmp for tmp in fields.split('&')]
}
name = d['newusername']
if name == '':
return 'You must enter a username'
if d['newpassword1'] != d['newpassword2']:
return "Your passwords don't match"
password = d['newpassword1']
if len(password) < 5:
return 'Your password needs to be at least 5 characters long'
email = d['email']
hashedpassword = hashlib.md5(
(password + salt).encode('utf-8')).hexdigest()
#CHECK IF USER ALREADY EXISTS!!!
db.execute(
""" INSERT INTO lab_members SET name=%s, password=%s, email=%s, viewtype='mouse', columns='mousename,,cagename,,cagename2,,genetics,,' """,
(name, hashedpassword, email))
return "Successfully added '{}' to database".format(name)
def addToDB(self, d): #
if d['cagename'] is None:
return "You must enter a name"
d['cagename'] = d['cagename'].lower()
d['name'] = d['cagename']
if db.execute("SELECT id FROM cages WHERE name=%s", (d['cagename'], ),
commit=False) != []:
return "The name '{}' is already in use.".format(d['cagename'])
d_cages = {}
for col in set(getAllColumns('cages')) & set(d.keys()):
d_cages[col] = d[col]
columns = ', '.join(list(d_cages.keys()))
parameters = ', '.join(
['%({0})s'.format(k) for k in list(d_cages.keys())])
query = 'INSERT INTO cages ({0}) VALUES ({1})'.format(
columns, parameters)
db.execute(query, d)
m_id = db.execute("SELECT id FROM cages where name=%s",
(d['cagename'], ))[0][0]
self.d = {"id": m_id, 'cagename': d['cagename']}
if d['caretaker'] is not None:
db.execute(
"INSERT INTO care_taker SET cage_id=%s, lab_member_id=(SELECT id FROM lab_members WHERE name=%s)",
(self.d['id'], d['caretaker']))
return "Successfully added cage"
def resetpassword(self, email):
names = DI.getusername(email)
if names is False:
return "That email address isn't associated with an account. Please enter a different email address."
subject = 'Password reset for Glams'
newpassword = ''.join(choice(chars) for x in range(10))
if len(names) == 1:
name = names[0]
text = 'Your user name is {} '.format(name)
text += ". Your new password is '{}'.".format(newpassword)
else:
text = 'The user names associated with this email address are: ' + ', '.join(
names)
text += ". The new password for those accounts is '{}'.".format(
newpassword)
html = '<p>{}</p>'.format(text)
hashedpassword = hashlib.md5(
(newpassword + salt).encode('utf-8')).hexdigest()
db.execute('''UPDATE lab_members SET password=%s WHERE email=%s ''',
(hashedpassword, email))
sendmail(email, subject, html, text)
return "Your password has been reset."
def sendusername(self, email):
names=data=db.execute('''SELECT name FROM lab_members WHERE email=%s ''',(email,), commit=False)
if len(data)==0:
return False
names=[]
for i in data:
names.append(i[0])
if names is False:
return "That email address isn't associated with an account. Please enter a different email address."
if len(names)==1:
name=names[0]
text='Your user name is {}'.format(name)
else:
text='The user names associated with this email address are: ' + ', '.join(names)
html='<p>{}</p>'.format(text)
subject='Your user name for RedRabbitAlmanac.com'
sendmail(email, subject, html, text)
return 'Your user name has been sent to {}'.format(email)
def sendusername(self, email):
names = data = db.execute(
'''SELECT name FROM lab_members WHERE email=%s ''', (email, ),
commit=False)
if len(data) == 0:
return False
names = []
for i in data:
names.append(i[0])
if names is False:
return "That email address isn't associated with an account. Please enter a different email address."
if len(names) == 1:
name = names[0]
text = 'Your user name is {}'.format(name)
else:
text = 'The user names associated with this email address are: ' + ', '.join(
names)
html = '<p>{}</p>'.format(text)
subject = 'Your user name for RedRabbitAlmanac.com'
sendmail(email, subject, html, text)
return 'Your user name has been sent to {}'.format(email)
def editOldCage(self,d):
s=''
parameters=[]
d['name']=d['cagename']
for col in set(getAllColumns('cages')) & set(d.keys()):
if d[col] is not None:
s+=" c.{}=%s,".format(col)
parameters.append(d[col])
else:
s+=" c.{}=NULL,".format(col)
parameters.append(self.d['id'])
s=s[:-1]
db.execute("UPDATE cages AS c, care_taker SET {0} WHERE id=%s".format(s),tuple(parameters))
if d['caretaker']!=self.d['caretaker']:
db.execute('DELETE FROM care_taker WHERE cage_id=%s',(self.d['id'],))
if d['caretaker']is not None:
db.execute("INSERT INTO care_taker SET cage_id=%s, lab_member_id=(SELECT id FROM lab_members WHERE name=%s)",(self.d['id'],d['caretaker']))
return 'Cage Updated'
def addToDB(self,d): #
if d['cagename'] is None:
return "You must enter a name"
d['cagename']=d['cagename'].lower()
d['name']=d['cagename']
if db.execute("SELECT id FROM cages WHERE name=%s",(d['cagename'],),commit=False) != []:
return "The name '{}' is already in use.".format(d['cagename'])
d_cages={}
for col in set(getAllColumns('cages')) & set(d.keys()):
d_cages[col]=d[col]
columns=', '.join(d_cages.keys())
parameters = ', '.join(['%({0})s'.format(k) for k in d_cages.keys()])
query = 'INSERT INTO cages ({0}) VALUES ({1})'.format(columns, parameters)
db.execute(query,d)
m_id=db.execute("SELECT id FROM cages where name=%s",(d['cagename'],))[0][0]
self.d={"id":m_id, 'cagename':d['cagename']}
if d['caretaker'] is not None:
db.execute("INSERT INTO care_taker SET cage_id=%s, lab_member_id=(SELECT id FROM lab_members WHERE name=%s)",(self.d['id'],d['caretaker']))
return "Successfully added cage"
def getGeneList():
genelist = db.execute("SELECT name FROM genes")
answer = ['']
answer.extend([gene[0] for gene in genelist])
return answer
def delete(self):
db.execute("DELETE FROM mice WHERE id=%s",(self.d['id'],))
return "Deleted mouse {}".format(self.d['name'])
def editOldMouse(self,d):
queries=[]
###### check for errors first #######
if d['mousename'] is None:
return "You must enter a name"
d['mousename']=d['mousename'].lower()
if ' ' in d['mousename']:
return 'You cannot have spaces in mouse names'
d['name']=d['mousename']
id_theif=db.execute("SELECT id FROM mice WHERE name=%s",(d['mousename'],),commit=False)
if id_theif != [] and id_theif[0][0]!=int(d['id']):
return "The name '{}' is already in use. id={},id={}".format(d['mousename'],id_theif[0][0],d['id'])
old=self.d
s=''
parameters=[]
for col in set(getAllColumns('mice')) & set(d.keys()):
if d[col] is not None:
s+=" m.{}=%s,".format(col)
parameters.append(d[col])
else:
s+=" m.{}=NULL,".format(col)
s=s[:-1]
parameters.append(self.d['id'])
queries.append(['UPDATE mice as m SET {0} WHERE id=%s'.format(s),tuple(parameters)])
### LINEAGE ###
if d['mother'] == old['mother'] and d['father'] == old['father']:
pass
elif d['mother'] is None and d['father'] is None:
queries.append(["DELETE FROM lineage WHERE child_id = %s",(self.d['id'],)])
elif (d['mother'] is not None and d['father'] is None) or (d['father'] is not None and d['mother'] is None):
return 'You need to enter both a mother and a father'
elif db.execute("SELECT id FROM mice WHERE name=%s",(d['mother'],))==[]:
return "The mother does not exist"
elif db.execute("SELECT id FROM mice WHERE name=%s",(d['father'],))==[]:
return "The father does not exist"
elif old['mother']=='' and old['father']=='':
queries.append(["INSERT INTO lineage SET mother_id=(SELECT id FROM mice WHERE name=%s), father_id=(SELECT id FROM mice WHERE name=%s), child_id =(SELECT id FROM mice WHERE name=%s) ",(d['mother'],d['father'],d['mousename'])])
else:
queries.append(["UPDATE lineage SET mother_id=(SELECT id FROM mice WHERE name=%s), father_id=(SELECT id FROM mice WHERE name=%s) WHERE child_id =(SELECT id FROM mice WHERE name=%s) ",(d['mother'],d['father'],d['mousename'])])
### HOUSING ###
if old['currentcage']==['','']:
if d['cagename'] is not None:
if db.execute("SELECT id FROM cages WHERE name=%s",(d['cagename'],))==[]:
return 'The cage you selected does not exist'
elif d['startDate'] is None:
return "You must add an 'occupied' date for the current cage"
else:
queries.append(["INSERT INTO housing SET mouse_id=(SELECT id FROM mice WHERE name=%s), cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, currentcage=1",(d['mousename'],d['cagename'],d['startDate'])])
elif d['cagename'] == old['currentcage'][0] and d['startDate'] == old['currentcage'][1]: #if the current cage and start date for that cage haven't changed, do nothing
pass
elif d['cagename'] is None:
queries.append(["DELETE FROM housing WHERE mouse_id=%s and currentcage=1",(self.d['id'],)])
elif d['startDate'] is None:
return "Your mouse needs to have a date occupied for the current cage"
elif db.execute("SELECT id FROM cages WHERE name=%s",(d['cagename'],))==[]:
return 'The cage you selected does not exist'
elif d['startDate'] is None:
return "You must add an 'occupied' date for the current cage"
else:
queries.append(["UPDATE housing SET cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s WHERE currentcage=1 AND mouse_id=(SELECT id FROM mice WHERE name=%s)",(d['cagename'],d['startDate'],d['mousename'])])
d['oldcages']=[]
for cage in d.keys():
if cage.startswith('oldcagename'):
n=str(cage.split('oldcagename')[1])
if d['oldcagename'+n] is not None:
if d['startDate'+n] is None or d['endDate'+n] is None:
return 'You must add two dates for old cages'
if d['startDate'+n]>=d['endDate'+n]:
return 'Your cage end date must be greater than the start date'
if d['cagename'] is not None and (d['startDate']<=d['startDate'+n] or d['startDate']<d['endDate'+n]):
return 'The current cage start date must be after all the other start dates and end dates.'
if db.execute("SELECT id from cages WHERE name=%s",(d['oldcagename'+n],))==[]:
return 'The cage "{}" does not exist'.format(d['oldcagename'+n])
d['oldcages'].append([d['oldcagename'+n], d['startDate'+n], d['endDate'+n]])
if sorted(d['oldcages'])!=sorted(old['oldcages']):
queries.append(["DELETE FROM housing WHERE mouse_id=%s AND currentcage=0",(self.d['id'],)])
n=0
for cage in d['oldcages']:
n=n+1
if db.execute("SELECT id FROM cages WHERE name=%s",(cage[0],))==[]:
return "The cage '{}' does not exist".format(cage[0])
else:
queries.append(["INSERT INTO housing SET mouse_id=(SELECT id FROM mice WHERE name=%s), cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, end_date=%s, currentcage=0 ",(d['mousename'], d['oldcagename'+str(n)], d['startDate'+str(n)], d['endDate'+str(n)])])
### EXPERIMENTS
newex=[]
dt=datetime.datetime.today()
today=datetime.datetime(dt.year,dt.month,dt.day)
for reserve in d.keys():
if reserve.startswith('reserve_lab_member'):
n=reserve.split('reserve_lab_member')[1]
if d['reserve_lab_member'+n] is not None:
try:
str2date(d['reserve_date'+n])
except (ValueError,AttributeError):
return "You must include a calendar date of the format '2016-08-02'"
if d['mousename']!=old['mousename']:
return "You can't edit a mouse's name if it already has an experiment."
if d['reserve_date'+n] is None or d['reserve_description'+n] is None or d['reserve_status'+n] is None:
return 'Your experiment needs a date, a type, and a status'
if str2date(d['reserve_date'+n])>today and d['reserve_status'+n]=='completed':
return "You can't have 'completed' an experiment scheduled for {0}.".format(d['reserve_date'+n])
if str2date(d['reserve_date'+n])<today and d['reserve_status'+n]=='planned':
return "You can't have a planned experiment scheduled for {0}.".format(d['reserve_date'+n])
# there was a bug in the following 3 lines of commented code which deleted all the other users experiments. I need to fix this in the future but don't have time right now.
# if d['reserve_lab_member'+n] != self.username:
# if len(old['experiments'])>=n and d['reserve_lab_member'+n] != old['experiments'][int(n)-1][0]:
# return "You can't record or plan experiments for other lab members."
else:
newex.append([d['reserve_lab_member'+n],d['reserve_date'+n],d['reserve_description'+n],d['reserve_notes'+n], d['reserve_status'+n], d['reserve_filenames'+n]])
if newex != ['','','','','','']:
if sorted(self.d['experiments'])==sorted(newex)[:-1]:
pass
elif self.d['experiments']!=['','','','','','']:
queries.append(["DELETE FROM experiments WHERE mouse_id=%s",(self.d['id'],)])
for x in newex:
queries.append(["INSERT INTO experiments SET mouse_id=%s, lab_member_id=(SELECT id FROM lab_members WHERE name=%s),date=%s, description=%s, notes=%s, status=%s, filenames=%s ",(self.d['id'],x[0],x[1],x[2],x[3],x[4],x[5])])
### GENETICS ###
newgenes=[]
for gene in d.keys():
if gene.startswith('gene'):
n=gene.split('gene')[1]
if d['gene'+n] is not None:
if d['zygosity'+n] is not None:
newgenes.append([d['gene'+n],d['zygosity'+n]])
else:
return "Select the zygosity of {}".format(d['gene'+n])
if newgenes != ['','']:
if sorted(self.d['genes'])==sorted(newgenes):
pass
elif self.d['genes']!=['','']:
queries.append(["DELETE FROM genetics WHERE mouse_id=%s",(self.d['id'],)])
if len([i[0] for i in newgenes]) > len(set([i[0] for i in newgenes])):
return 'Your list of genes is not unique'
for gene in newgenes:
queries.append(["INSERT INTO genetics SET mouse_id=%s, gene_id=(SELECT id FROM genes WHERE name=%s), zygosity=%s",(self.d['id'],gene[0],gene[1])])
### Actually perform the queries. Hopefully there aren't any errors here, as they won't show up in the browser.
for query in queries:
db.execute(query[0],query[1],commit=True)
### KICK OUT OF HOME IF MOUSE IS SAC'D
if self.d['life_status']=='alive' and d['life_status']!='alive':
if old['currentcage'] != ['','']: #if the mouse was in a cage, kick it out
self.removeFromCage()
return "Mouse updated"
def addToDB(self,d):
queries=[]
###### check for errors first #######
if d['mousename'] is None:
return "You must enter a name"
d['mousename']=d['mousename'].lower()
if ' ' in d['mousename']:
return 'You cannot have spaces in mouse names'
d['name']=d['mousename']
if db.execute("SELECT id FROM mice WHERE name=%s",(d['mousename'],),commit=False) != []:
return "The name '{}' is already in use.".format(d['mousename'])
### MICE ###
s=''
parameters=[]
for col in set(getAllColumns('mice')) & set(d.keys()):
if d[col] is not None:
s+=" {}=%s,".format(col)
parameters.append(d[col])
s=s[:-1]
queries.append(['INSERT INTO mice SET {}'.format(s),tuple(parameters)])
### LINEAGE ###
if d['mother'] is not None or d['father'] is not None:
if (d['mother'] is not None and d['father'] is None) or (d['father'] is not None and d['mother'] is None):
return 'You need to enter both a mother and a father'
elif db.execute("SELECT id FROM mice WHERE name=%s",(d['mother'],))==[]:
return "The mother does not exist"
elif db.execute("SELECT id FROM mice WHERE name=%s",(d['father'],))==[]:
return "The father does not exist"
else:
queries.append(["INSERT INTO lineage SET mother_id=(SELECT id FROM mice WHERE name=%s), father_id=(SELECT id FROM mice WHERE name=%s), child_id =(SELECT id FROM mice WHERE name=%s)",(d['mother'],d['father'],d['mousename'])])
### HOUSING ###
if d['cagename'] is not None:
if db.execute("SELECT id from cages WHERE name=%s",(d['cagename'],))==[]:
return 'The cage you selected does not exist'
elif d['startDate'] is not None:
queries.append(["INSERT INTO housing SET mouse_id=(SELECT id FROM mice WHERE name=%s), cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, currentcage=1",(d['mousename'],d['cagename'],d['startDate'])])
else:
return "You must add an 'occupied' date for the current cage"
for cage in d.keys():
if cage.startswith('oldcagename'):
n=cage.split('oldcagename')[1]
if d['oldcagename'+n] is not None:
if d['startDate'+n] is None or d['endDate'+n] is None:
return 'You must add two dates for old cages'
if d['startDate'+n]>=d['endDate'+n]:
return 'Your cage end date must be greater than the start date'
if d['cagename'] is not None and (d['startDate']<=d['startDate'+n] or d['startDate']<d['endDate'+n]):
return 'The current cage start date must be after all the other start dates and end dates.'
if db.execute("SELECT id from cages WHERE name=%s",(d['oldcagename'+n],))==[]:
return 'The cage "{}" does not exist'.format(d['oldcagename'+n])
queries.append(["INSERT INTO housing SET mouse_id=(SELECT id FROM mice WHERE name=%s), cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, end_date=%s, currentcage=0 ",(d['mousename'], d['oldcagename'+n], d['startDate'+n], d['endDate'+n])])
### EXPERIMENTS
newex=[]
dt=datetime.datetime.today()
today=datetime.datetime(dt.year,dt.month,dt.day)
for reserve in d.keys():
if reserve.startswith('reserve_lab_member'):
n=reserve.split('reserve_lab_member')[1]
if d['reserve_lab_member'+n] is not None:
try:
str2date(d['reserve_date'+n])
except (ValueError,AttributeError):
return "You must include a calendar date of the format '2016-08-02'"
if d['reserve_date'+n] is None or d['reserve_description'+n] is None or d['reserve_status'+n] is None:
return 'Your experiment needs a date, a type, and a status'
elif str2date(d['reserve_date'+n])>today and d['reserve_status'+n]=='completed':
return "You can't have 'completed' an experiment scheduled for {0}.".format(d['reserve_date'+n])
elif str2date(d['reserve_date'+n])<today and d['reserve_status'+n]=='planned':
return "You can't have a planned experiment scheduled for {0}.".format(d['reserve_date'+n])
elif d['reserve_lab_member'+n] != self.username:
return "You can't record or plan experiments for other lab members."
else:
newex.append([d['reserve_lab_member'+n],d['reserve_date'+n],d['reserve_description'+n],d['reserve_notes'+n], d['reserve_status'+n], d['reserve_filenames'+n]])
if newex != ['','','','','','']:
for x in newex:
queries.append(["INSERT INTO experiments SET mouse_id=(SELECT id FROM mice WHERE name=%s), lab_member_id=(SELECT id FROM lab_members WHERE name=%s),date=%s, description=%s, notes=%s, status=%s, filenames=%s ",(d['mousename'],x[0],x[1],x[2],x[3],x[4],x[5])])
### GENETICS ###
newgenes=[]
for gene in d.keys():
if gene.startswith('gene'):
n=gene.split('gene')[1]
if d['gene'+n] is not None:
if d['zygosity'+n] is not None:
newgenes.append([d['gene'+n],d['zygosity'+n]])
else:
return "Select the zygosity of {}".format(d['gene'+n])
if newgenes != ['','']:
if len([i[0] for i in newgenes]) > len(set([i[0] for i in newgenes])):
return 'Your list of genes is not unique'
for gene in newgenes:
queries.append(["INSERT INTO genetics SET mouse_id=(SELECT id FROM mice WHERE name=%s), gene_id=(SELECT id FROM genes WHERE name=%s), zygosity=%s",(d['mousename'],gene[0],gene[1])])
### Actually perform the queries. Hopefully there aren't any errors here, as they won't show up in the browser.
for query in queries:
db.execute(query[0],query[1],commit=True)
return "Successfully added '{}'! ".format(d['mousename'])
def getLabMemberList():
labmemberlist=db.execute("SELECT name FROM lab_members")
answer=['']
answer.extend([lm[0] for lm in labmemberlist])
return answer
def getAdmin():
username = checkPassword()
if not username or username != 'admin':
return """<meta http-equiv="refresh" content="0;url=/home/login" />"""
######################## CREATE ARTICLE ###############################################################################
###### ADMIN
changepass = E.div(
E.h2('Change admin password:'******'Current password:'******'name': 'oldpassword',
'type': 'password'
})),
E.div(E.label('New password:'******'name': 'newpassword1',
'type': 'password'
})),
E.div(E.label('Confirm new password:'******'name': 'newpassword2',
'type': 'password'
})),
E.a(
{
'class': 'button-link',
'onclick': "changepassword($(this).parent().serialize());"
}, 'Change password')))
changeemail = E.div(
E.h2('Change admin email address:'),
E.form(
E.div(
E.label('Password:'******'name': 'password',
'id': 'emailpassword',
'type': 'password'
})),
E.div(
E.label('New email:'),
E.input({
'name': 'newemail1',
'id': 'newemail1',
'type': 'text'
})),
E.div(
E.label('Confirm new email:'),
E.input({
'name': 'newemail2',
'id': 'newemail2',
'type': 'text'
})),
E.a(
{
'class': 'button-link',
'onclick': "changeemail($(this).parent().serialize());"
}, 'Change email')))
###### USERS
adduser = E.div(
E.h2('Add User:'******'id': 'adduserform'},
E.div(E.label('User name:'),
E.input({
'name': 'newusername',
'type': 'text'
})),
E.div(E.label('Email address:'),
E.input({
'name': 'email',
'type': 'text'
})),
E.div(E.label('User password:'******'name': 'newpassword1',
'type': 'password'
})),
E.div(E.label('Confirm user password:'******'name': 'newpassword2',
'type': 'password'
})),
E.a(
{
'class': 'button-link',
'onclick': "adduser($(this).parent().serialize());"
}, 'Add user')))
userlist = E.select({'name': 'user'})
vals = db.execute("SELECT name FROM lab_members")
if vals == []:
userlist.append(E.option(''))
else:
for val in vals:
userlist.append(E.option(val[0]))
removeuser = E.div(
E.h2('Remove User:'******'User name:'), userlist),
E.a(
{
'class': 'button-link',
'onclick': "removeuser($(this).parent().serialize());"
}, 'Remove user')))
##### GENES
addgene = E.div(
E.h2('Add Gene:'),
E.form(
E.div(E.label('Gene Name:'),
E.input({
'name': 'genename',
'type': 'text'
})),
E.div(
E.label('Wild Type Presence:'),
E.select({'name': 'genedefaultstatus'}, E.option('+'),
E.option('-'))),
E.a(
{
'class': 'button-link',
'onclick': "addgene($(this).parent().serialize());"
}, 'Add gene')))
genelist = E.select({'name': 'gene'})
vals = db.execute("SELECT name FROM genes")
if vals == []:
genelist.append(E.option(''))
else:
for val in vals:
genelist.append(E.option(val[0]))
removegene = E.div(
E.h2('Remove Gene:'),
E.form(
E.div(E.label('Gene Name:'), genelist),
E.a(
{
'class': 'button-link',
'onclick': "removegene($(this).parent().serialize());"
}, 'Remove gene')))
##### STRAINS
addstrain = E.div(
E.h2('Add Strain:'),
E.form(
E.div(E.label('Strain Name:'),
E.input({
'name': 'strainname',
'type': 'text'
})),
E.a(
{
'class': 'button-link',
'onclick': "addstrain($(this).parent().serialize());"
}, 'Add Strain')))
strainlist = E.select({'name': 'strain'})
vals = db.execute("SELECT name FROM strains")
if vals == []:
strainlist.append(E.option(''))
else:
for val in vals:
strainlist.append(E.option(val[0]))
removestrain = E.div(
E.h2('Remove strain:'),
E.form({'class': 'form'}, E.div(E.label('Strain Name:'), strainlist),
E.a(
{
'class': 'button-link',
'onclick': "removestrain($(this).parent().serialize());"
}, 'Remove strain')))
### PUT ALL THE PIECES TOGETHER
article = E.div(
E.div({'id': 'notification'}, ''),
E.div({'class': 'tabs'},
E.ul(E.li(E.a({'href': '#tab1'}, 'Admin')),
E.li(E.a({'href': '#tab2'}, 'Users')),
E.li(E.a({'href': '#tab3'}, 'Genes')),
E.li(E.a({'href': '#tab4'}, 'Strains'))),
E.div(
E.div({'id': 'tab1'}, changepass, changeemail),
E.div({'id': 'tab2'}, adduser, removeuser),
E.div({'id': 'tab3'}, addgene, removegene),
E.div({'id': 'tab4'}, addstrain, removestrain),
)))
article = etree.tostring(article, pretty_print=True)
####################### STYLE AND JAVASCRIPT #############################################################################
style = """
h2 {
margin-bottom: 10px;
padding-bottom: 5px;
border-bottom: 1px solid #D8D8D8;
}
input{
webkit-border-radius: 8px;
-moz-border-radius: 8px;
border-radius: 8px;
padding: 4px;
}
table{
width:500px;
margin:10px;
}
#notification{
display:none;
padding:5px;
margin:3px;
background-color:#FFFF66;
webkit-border-radius: 8px;
-moz-border-radius: 8px;
border-radius: 8px;}
label{
display: inline-block;
float: left;
clear: left;
width: 200px;
margin-right: 10px;
white-space: nowrap;}
form .button-link{
margin-left:0px;
}
form div{margin-bottom:10px;}
"""
javascript = """
var t;
function notify(data){
$('#notification').html(data);
$('#notification').show();
var fadefunc="$('#notification').hide('fade', {}, 200);";
t=setTimeout(fadefunc,15000);
}
function changepassword(fields){
$.post('/home/ajax/changepassword/',{fields:fields}, function(data){ notify(data);});}
function changeemail(fields){
alert(fields);
$.post('/home/ajax/changeemail/',{fields:fields}, function(data){ notify(data);});}
function adduser(fields){
$.post('/admin/adduser/',{fields:fields}, function(data){ notify(data);});}
function removeuser(fields){
$.post('/admin/removeuser/',{fields:fields}, function(data){ notify(data);});}
function addgene(fields){
$.post('/admin/addgene/',{fields:fields}, function(data){ notify(data);refreshgenes();});}
function removegene(fields){
$.post('/admin/removegene/',{fields:fields}, function(data){ notify(data);refreshgenes();});}
function refreshgenes(){
$.post('/admin/refreshgenes/', function(data){ $("select[name='gene']").html(data);});}
function addstrain(fields){
$.post('/admin/addstrain/',{fields:fields}, function(data){ notify(data);refreshstrains();});}
function removestrain(fields){
$.post('/admin/removestrain/',{fields:fields}, function(data){ notify(data);refreshstrains();});}
function refreshstrains(){
$.post('/admin/refreshstrains/', function(data){ $("select[name='strain']").html(data);});}
$(document).ready(function(){
$( ".tabs" ).tabs();
});
"""
resources = "<style type='text/css'>" + style + '</style><script type="text/javascript">' + javascript + '</script>'
return glamsTemplate(article, username, resources=resources)
def getAdmin():
username=checkPassword()
if not username or username!='admin':
return """<meta http-equiv="refresh" content="0;url=/home/login" />"""
######################## CREATE ARTICLE ###############################################################################
###### ADMIN
changepass=E.div(E.h2('Change admin password:'******'Current password:'******'name':'oldpassword', 'type':'password'})),
E.div(E.label('New password:'******'name':'newpassword1', 'type':'password'})),
E.div(E.label('Confirm new password:'******'name':'newpassword2', 'type':'password'})),
E.a({'class':'button-link','onclick':"changepassword($(this).parent().serialize());"},'Change password')))
changeemail=E.div(E.h2('Change admin email address:'),
E.form(
E.div(E.label('Password:'******'name':'password','id':'emailpassword', 'type':'password'})),
E.div(E.label('New email:'), E.input({'name':'newemail1', 'id':'newemail1','type':'text'})),
E.div(E.label('Confirm new email:'), E.input({'name':'newemail2', 'id':'newemail2','type':'text'})),
E.a({'class':'button-link','onclick':"changeemail($(this).parent().serialize());"},'Change email')))
###### USERS
adduser=E.div(E.h2('Add User:'******'id':'adduserform'},
E.div(E.label('User name:'),E.input({'name':'newusername', 'type':'text'})),
E.div(E.label('Email address:'),E.input({'name':'email', 'type':'text'})),
E.div(E.label('User password:'******'name':'newpassword1', 'type':'password'})),
E.div(E.label('Confirm user password:'******'name':'newpassword2', 'type':'password'})),
E.a({'class':'button-link','onclick':"adduser($(this).parent().serialize());"},'Add user')))
userlist=E.select({'name':'user'})
vals=db.execute("SELECT name FROM lab_members")
if vals==[]:
userlist.append(E.option(''))
else:
for val in vals:
userlist.append(E.option(val[0]))
removeuser=E.div(E.h2('Remove User:'******'User name:'),userlist),
E.a({'class':'button-link','onclick':"removeuser($(this).parent().serialize());"},'Remove user')))
##### GENES
addgene=E.div(E.h2('Add Gene:'),
E.form(
E.div(E.label('Gene Name:'),E.input({'name':'genename','type':'text'})),
E.div(E.label('Wild Type Presence:'),E.select({'name':'genedefaultstatus'},E.option('+'),E.option('-') )),
E.a({'class':'button-link','onclick':"addgene($(this).parent().serialize());"},'Add gene')))
genelist=E.select({'name':'gene'})
vals=db.execute("SELECT name FROM genes")
if vals==[]:
genelist.append(E.option(''))
else:
for val in vals:
genelist.append(E.option(val[0]))
removegene=E.div(E.h2('Remove Gene:'),
E.form(
E.div(E.label('Gene Name:'),genelist),
E.a({'class':'button-link','onclick':"removegene($(this).parent().serialize());"},'Remove gene')))
##### STRAINS
addstrain=E.div(E.h2('Add Strain:'),
E.form(
E.div(E.label('Strain Name:'),E.input({'name':'strainname','type':'text'})),
E.a({'class':'button-link','onclick':"addstrain($(this).parent().serialize());"},'Add Strain')))
strainlist=E.select({'name':'strain'})
vals=db.execute("SELECT name FROM strains")
if vals==[]:
strainlist.append(E.option(''))
else:
for val in vals:
strainlist.append(E.option(val[0]))
removestrain=E.div(E.h2('Remove strain:'),
E.form({'class':'form'},
E.div(E.label('Strain Name:'),strainlist),
E.a({'class':'button-link','onclick':"removestrain($(this).parent().serialize());"},'Remove strain')))
### PUT ALL THE PIECES TOGETHER
article=E.div(
E.div({'id':'notification'},''),
E.div({'class':'tabs'},
E.ul(E.li(E.a({'href':'#tab1'},'Admin')),
E.li(E.a({'href':'#tab2'},'Users')),
E.li(E.a({'href':'#tab3'},'Genes')),
E.li(E.a({'href':'#tab4'},'Strains'))),
E.div(
E.div({'id':'tab1'},changepass, changeemail),
E.div({'id':'tab2'},adduser, removeuser),
E.div({'id':'tab3'}, addgene,removegene),
E.div({'id':'tab4'}, addstrain, removestrain),
)
)
)
article=etree.tostring(article, pretty_print=True)
####################### STYLE AND JAVASCRIPT #############################################################################
style="""
h2 {
margin-bottom: 10px;
padding-bottom: 5px;
border-bottom: 1px solid #D8D8D8;
}
input{
webkit-border-radius: 8px;
-moz-border-radius: 8px;
border-radius: 8px;
padding: 4px;
}
table{
width:500px;
margin:10px;
}
#notification{
display:none;
padding:5px;
margin:3px;
background-color:#FFFF66;
webkit-border-radius: 8px;
-moz-border-radius: 8px;
border-radius: 8px;}
label{
display: inline-block;
float: left;
clear: left;
width: 200px;
margin-right: 10px;
white-space: nowrap;}
form .button-link{
margin-left:0px;
}
form div{margin-bottom:10px;}
"""
javascript="""
var t;
function notify(data){
$('#notification').html(data);
$('#notification').show();
var fadefunc="$('#notification').hide('fade', {}, 200);";
t=setTimeout(fadefunc,15000);
}
function changepassword(fields){
$.post('/home/ajax/changepassword/',{fields:fields}, function(data){ notify(data);});}
function changeemail(fields){
alert(fields);
$.post('/home/ajax/changeemail/',{fields:fields}, function(data){ notify(data);});}
function adduser(fields){
$.post('/admin/adduser/',{fields:fields}, function(data){ notify(data);});}
function removeuser(fields){
$.post('/admin/removeuser/',{fields:fields}, function(data){ notify(data);});}
function addgene(fields){
$.post('/admin/addgene/',{fields:fields}, function(data){ notify(data);refreshgenes();});}
function removegene(fields){
$.post('/admin/removegene/',{fields:fields}, function(data){ notify(data);refreshgenes();});}
function refreshgenes(){
$.post('/admin/refreshgenes/', function(data){ $("select[name='gene']").html(data);});}
function addstrain(fields){
$.post('/admin/addstrain/',{fields:fields}, function(data){ notify(data);refreshstrains();});}
function removestrain(fields){
$.post('/admin/removestrain/',{fields:fields}, function(data){ notify(data);refreshstrains();});}
function refreshstrains(){
$.post('/admin/refreshstrains/', function(data){ $("select[name='strain']").html(data);});}
$(document).ready(function(){
$( ".tabs" ).tabs();
});
"""
resources="<style type='text/css'>"+style+'</style><script type="text/javascript">'+javascript+'</script>'
return glamsTemplate(article, username, resources=resources)
def removeFromCage(self):
db.execute("UPDATE housing SET end_date=%s, currentcage=0 WHERE mouse_id=%s AND currentcage=1",(date2str(datetime.datetime.now()),self.d['id']))
return '{0} is now cageless'.format(self.d['mousename'])
def getGeneList():
genelist=db.execute("SELECT name FROM genes")
answer=['']
answer.extend([gene[0] for gene in genelist])
return answer
def getStrainList():
strainlist = db.execute("SELECT name FROM strains")
answer = ['']
answer.extend([strain[0] for strain in strainlist])
return answer
def transfer(self,newcage):
print(newcage)
with lock: ## sometimes the javascript calls ajax/moveMouse twice, and they execute concurrently. This prevents multiple cherrypy threads from calling this function while it is being executed.
cage_id=db.execute("SELECT name, start_date FROM housing INNER JOIN cages ON housing.cage_id=cages.id WHERE mouse_id=%s AND currentcage=1",(self.d['id'],))
today=datetime.datetime.now()
if cage_id==[]:
db.execute("INSERT INTO housing SET mouse_id=%s, cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, currentcage=1",(self.d['id'],newcage,date2str(today)))
answer= "{0} placed in {1} on {2}".format(self.d['mousename'],newcage,date2str(today))
else:
print(cage_id)
oldcage=cage_id[0][0]
if oldcage==newcage:
return "{} still in cage {}".format(self.d['mousename'],newcage)
start_date=cage_id[0][1]
if date2str(start_date)==date2str(today): #if the old cage's start_date was today, erase mention of the previous cage. Animals have to have been in a cage for 1 day or more for the record to be kept.
db.execute("UPDATE housing SET cage_id=(SELECT id FROM cages WHERE name=%s) WHERE mouse_id=%s AND currentcage=1",(newcage, self.d['id']))
else: # if the pretransfer cage didn't have today as its first day:
db.execute("UPDATE housing SET currentcage=0, end_date=%s WHERE mouse_id=%s AND currentcage=1",(date2str(today),self.d['id'])) #
db.execute("INSERT INTO housing SET mouse_id=%s, cage_id=(SELECT id FROM cages WHERE name=%s), start_date=%s, currentcage=1",(self.d['id'],newcage,date2str(today)))
### now merge entries which have the same mouse, cage, and where one entry's stop date is another entries start date.
overlapping_cages=db.execute("""SELECT h1.cage_id, h2.start_date AS common_date, h1.start_date from housing as h1 INNER JOIN housing AS h2 ON h1.mouse_id=h2.mouse_id AND h1.end_date=h2.start_date AND h1.cage_id=h2.cage_id WHERE h1.mouse_id=%s""",(self.d['id'],))
for cage in overlapping_cages:
db.execute("DELETE FROM housing WHERE mouse_id=%s AND cage_id=%s AND end_date=%s ",(self.d['id'], cage[0], date2str(cage[1])))
db.execute("UPDATE housing SET start_date=%s WHERE mouse_id=%s AND cage_id=%s AND start_date=%s ",(date2str(cage[2]), self.d['id'], cage[0], date2str(cage[1])))
answer= "{0} transfered from {1} to {2} on {3}".format(self.d['mousename'],oldcage,newcage,date2str(today))
return answer+','+ etree.tostring(getResident(self.d), pretty_print=True)
def getLabMemberList():
labmemberlist = db.execute("SELECT name FROM lab_members")
answer = ['']
answer.extend([lm[0] for lm in labmemberlist])
return answer
def getStrainList():
strainlist=db.execute("SELECT name FROM strains")
answer=['']
answer.extend([strain[0] for strain in strainlist])
return answer
