def getConfigReplicate(request, id = None): # If id is a number if id and not 'None' in id: SQL = """ SELECT v_replicates.filename, v_replicates.starttime, v_replicates.endtime, v_replicates.movement, v_replicates.runningtime FROM v_replicates WHERE configurationid = %(id)s ORDER BY v_replicates.id""" return selectQuery(request,SQL, {'id': id}) else: SQL = """ SELECT v_replicates.filename, v_replicates.starttime, v_replicates.endtime, v_replicates.movement, v_replicates.runningtime FROM v_replicates WHERE configurationid IS NULL ORDER BY v_replicates.id""" return selectQuery(request,SQL)
def getStudyReplicates(request, studyid = False): # if studyid is a number if studyid and not 'None' in studyid: SQL = """ SELECT v_replicates.id, v_replicates.filename, v_replicates.starttime, v_replicates.endtime, v_replicates.movement, v_replicates.runningtime FROM study LEFT JOIN configuration ON configuration.studyID = %(id)s INNER JOIN v_replicates ON v_replicates.configurationid = configuration.id WHERE study.id = %(id)s ORDER BY v_replicates.starttime desc""" return selectQuery(request, SQL,{'id':studyid}) else: SQL = """ SELECT v_replicates.id, v_replicates.filename, v_replicates.starttime, v_replicates.endtime, v_replicates.movement, v_replicates.runningtime FROM configuration INNER JOIN v_replicates ON v_replicates.configurationid = configuration.id WHERE studyid IS NULL ORDER BY v_replicates.starttime desc""" return selectQuery(request,SQL)
def getLongRunningReplicates(request): SQL = """ SELECT filename, starttime, movement, (now() - starttime) AS runningtime, id FROM v_replicates WHERE endtime IS null AND (now() - starttime) > interval '2 days' ORDER BY runningtime DESC""" return selectQuery(request, SQL)
def getStudyConfigurations(request, studyid = None): # If studyid is a number. if studyid and not 'None' in studyid: SQL = """ SELECT configuration.filename, concat('(', ncols, ', ', nrows, ', ', xllcorner, ', ', yllcorner, ', ', cellsize, ')') as spatial, count(replicate.id), configuration.id FROM configuration left join replicate on replicate.configurationid = configuration.id WHERE studyid = %(id)s group by configuration.id order by configuration.id""" return selectQuery(request, SQL, {'id':studyid}) # If studyid is False. else: SQL=""" SELECT configuration.filename, concat('(', ncols, ', ', nrows, ', ', xllcorner, ', ', yllcorner, ', ', cellsize, ')') as spatial, count(replicate.id), configuration.id FROM configuration left join replicate on replicate.configurationid = configuration.id WHERE studyid is NULL group by configuration.id order by configuration.id""" return selectQuery(request, SQL)
def getReplicates(request, running, limit = 1000): SQL = """ SELECT id, filename, starttime, endtime, movement, CASE WHEN endtime IS NULL THEN (now() - starttime) ELSE runningtime END AS runningtime FROM v_replicates {} ORDER BY starttime DESC LIMIT %(limit)s""" WHERE = "WHERE (now() - starttime) <= interval '3 days' AND endtime IS NULL" # Update the query and return the results append = WHERE if running else "" return selectQuery(request, SQL.format(append), {'limit': limit})
def getStudies(request): SQL = """ SELECT s.name,s.id,COUNT(DISTINCT c.id) configs, COUNT(DISTINCT r.id) replicates FROM sim.study s LEFT JOIN sim.configuration c ON c.studyid = s.id LEFT JOIN v_replicates r on r.configurationid = c.id GROUP BY s.id UNION SELECT CASE WHEN name IS NULL THEN 'Unassigned' ELSE name END, studyid,configs, replicates FROM ( SELECT s.id, studyid, s.name, COUNT(DISTINCT c.id) configs, COUNT(DISTINCT r.id) replicates FROM sim.configuration c LEFT JOIN sim.replicate r ON r.configurationid = c.id LEFT JOIN sim.study s ON s.id = c.studyid GROUP BY s.id, studyid, s.name) iq order by id""" return selectQuery(request, SQL)
def getStudyNotes(request, id): SQL = """ SELECT * FROM notes WHERE studyid = %(id)s ORDER BY date DESC""" return selectQuery(request, SQL,{"id":id})