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