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})
