def rnadashboard_technologies(dbs, confs):
"""Return the technologies for the RNA dashboard."""
description = [('Id', 'string'),
('Title', 'string'),
('Description', 'string'),
('URL', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select name,
displayName,
description,
descriptionUrl
from technology"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
results = []
for row in rows:
results.append((row[0], row[1], escape_html(row[2]), row[3]))
chart['table_data'] = results
return chart
def rnadashboard_technologies(dbs, confs):
"""Return the technologies for the RNA dashboard."""
description = [
('Id', 'string'),
('Title', 'string'),
('Description', 'string'),
('URL', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select name,
displayName,
description,
descriptionUrl
from technology"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
results = []
for row in rows:
results.append((row[0], row[1], escape_html(row[2]), row[3]))
chart['table_data'] = results
return chart
def rnadashboard_files(dbs, confs):
"""Return the files for the RNA dashboard.
See
http://genome-test.cse.ucsc.edu/ENCODE/otherTerms.html
and
http://genome-test.cse.ucsc.edu/cgi-bin/hgEncodeVocab?type=%22typeOfTerm%22
for more info about file attributes.
"""
description = [('Url', 'string'),
('Attributes', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select file.url,
file.allAttributes
from
file
"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
chart['table_data'] = rows
return chart
def rnadashboard_files(dbs, confs):
"""Return the files for the RNA dashboard.
See
http://genome-test.cse.ucsc.edu/ENCODE/otherTerms.html
and
http://genome-test.cse.ucsc.edu/cgi-bin/hgEncodeVocab?type=%22typeOfTerm%22
for more info about file attributes.
"""
description = [
('Url', 'string'),
('Attributes', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select file.url,
file.allAttributes
from
file
"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
chart['table_data'] = rows
return chart
def rnadashboard_rna_fractions(dbs, confs):
"""Return the RNA fractions for the RNA dashboard."""
description = [('Id', 'string'),
('Title', 'string'),
('Description', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select ucscName,
displayName,
description
from rnaExtract"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
chart['table_data'] = rows
return chart
def rnadashboard_rna_fractions(dbs, confs):
"""Return the RNA fractions for the RNA dashboard."""
description = [
('Id', 'string'),
('Title', 'string'),
('Description', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
select ucscName,
displayName,
description
from rnaExtract"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
chart['table_data'] = rows
return chart
def _fastqs(dbs, confs, wheres=""):
"""Return the fastq files only."""
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
selects = ["file.dateSubmitted",
"file.experiment_data_processing",
"file.fileType",
"fileView.displayName",
"fileView.name",
"file.lab as endLab", # Produced the file using their pipeline
"file.url",
"file.size",
"fileView.deNovo",
"sample.grantName",
"cell.displayName as cellName",
"sample.cell",
"cell.tier",
"cell.sex",
"localization.displayName as localization",
"localization.ucscName",
"rnaExtract.displayName as rnaExtract",
"rnaExtract.ucscName",
"technology.displayName as technology",
"technology.name",
"file.atUcsc",
"fileType.rawType",
"sample.replicate as bioRep",
"sample.id as sample",
"sample.internalName",
"experiment.lab as expLab", # Did the physical experiment
"experiment.readType",
"experiment.insertLength",
"experiment.techReplicate as techRep",
"experiment.id as expId",
"file.allAttributes"]
sql = """
SELECT %s
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName
AND
file.fileType = "FASTQ"
AND
technology.name = "RNASEQ"
%s""" % (",".join(selects), wheres)
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
result = []
for row in rows:
result.append(dict(zip(selects, row)))
return result
def _rnadashboard_results_sql(dbs, confs, wheres=""):
"""Query the database for the RNA dashboard."""
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
SELECT file.dateSubmitted,
file.fileType,
fileView.displayName,
file.lab as endLab,
file.url,
file.size,
fileView.deNovo,
sample.grantName,
cell.displayName as cellName,
sample.cell,
cell.tier,
localization.displayName as localization,
localization.ucscName,
rnaExtract.displayName as rnaExtract,
rnaExtract.ucscName,
technology.displayName as technology,
technology.name,
file.atUcsc,
fileType.rawType,
sample.replicate as bioRep,
sample.id as sample,
sample.internalName,
experiment.lab as expLab,
experiment.readType,
experiment.insertLength,
experiment.techReplicate as techRep,
experiment.id as expId
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName
%s""" % wheres
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def rnadashboard(dbs, confs):
"""Return RNA dashboard."""
description = [('Sample Grant Name', 'string'),
('Cell Type', 'string'),
('Cell Type Id', 'string'),
('Tier', 'number'),
('Localization', 'string'),
('Localization Id', 'string'),
('RNA Extract', 'string'),
('RNA Extract Id', 'string'),
('Technology', 'string'),
('Technology Id', 'string'),
('File at UCSC', 'number'),
('File Raw Type', 'number'),
('Sample Replicate', 'number'),
('Sample Id', 'string'),
('Sample Internal Name', 'string'),
('Replicate Lab', 'string'),
('Replicate Read Type', 'string'),
('Replicate Insert Length', 'string'),
('Replicate Tech Replicate', 'number'),
('Replicate Id', 'string'),
('File Type', 'string'),
('File View', 'string'),
('File Lab', 'string'),
('File URL', 'string'),
('File Size', 'string'),
('File View de novo', 'number'),
('Restricted until', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
SELECT sample.grantName,
cell.displayName as cellName,
sample.cell,
cell.tier,
localization.displayName as localization,
localization.ucscName,
rnaExtract.displayName as rnaExtract,
rnaExtract.ucscName,
technology.displayName as technology,
technology.name,
file.atUcsc,
fileType.rawType,
sample.replicate as bioRep,
sample.id as sample,
sample.internalName,
experiment.lab as expLab,
experiment.readType,
experiment.insertLength,
experiment.techReplicate as techRep,
experiment.id as expId,
file.fileType,
fileView.displayName,
file.lab as endLab,
file.url,
file.size,
fileView.deNovo,
file.dateSubmitted
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
results = []
for row in rows:
result = list(row)
if row[10] == 1:
if not result[-1] is None:
end = result[-1] + datetime.timedelta(9 * 365 / 12)
if end > datetime.date.today():
result[-1] = "%s-%s-%s" % (end.year, end.month, end.day)
else:
result[-1] = None
else:
result[-1] = 'To be decided'
results.append(result)
chart['table_data'] = results
return chart
def _fastqs(dbs, confs, wheres=""):
"""Return the fastq files only."""
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
selects = [
"file.dateSubmitted",
"file.experiment_data_processing",
"file.fileType",
"fileView.displayName",
"fileView.name",
"file.lab as endLab", # Produced the file using their pipeline
"file.url",
"file.size",
"fileView.deNovo",
"sample.grantName",
"cell.displayName as cellName",
"sample.cell",
"cell.tier",
"cell.sex",
"localization.displayName as localization",
"localization.ucscName",
"rnaExtract.displayName as rnaExtract",
"rnaExtract.ucscName",
"technology.displayName as technology",
"technology.name",
"file.atUcsc",
"fileType.rawType",
"sample.replicate as bioRep",
"sample.id as sample",
"sample.internalName",
"experiment.lab as expLab", # Did the physical experiment
"experiment.readType",
"experiment.insertLength",
"experiment.techReplicate as techRep",
"experiment.id as expId",
"file.allAttributes"
]
sql = """
SELECT %s
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName
AND
file.fileType = "FASTQ"
AND
technology.name = "RNASEQ"
%s""" % (",".join(selects), wheres)
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
result = []
for row in rows:
result.append(dict(zip(selects, row)))
return result
def _rnadashboard_results_sql(dbs, confs, wheres=""):
"""Query the database for the RNA dashboard."""
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
SELECT file.dateSubmitted,
file.fileType,
fileView.displayName,
file.lab as endLab,
file.url,
file.size,
fileView.deNovo,
sample.grantName,
cell.displayName as cellName,
sample.cell,
cell.tier,
localization.displayName as localization,
localization.ucscName,
rnaExtract.displayName as rnaExtract,
rnaExtract.ucscName,
technology.displayName as technology,
technology.name,
file.atUcsc,
fileType.rawType,
sample.replicate as bioRep,
sample.id as sample,
sample.internalName,
experiment.lab as expLab,
experiment.readType,
experiment.insertLength,
experiment.techReplicate as techRep,
experiment.id as expId
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName
%s""" % wheres
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
return rows
def rnadashboard(dbs, confs):
"""Return RNA dashboard."""
description = [
('Sample Grant Name', 'string'),
('Cell Type', 'string'),
('Cell Type Id', 'string'),
('Tier', 'number'),
('Localization', 'string'),
('Localization Id', 'string'),
('RNA Extract', 'string'),
('RNA Extract Id', 'string'),
('Technology', 'string'),
('Technology Id', 'string'),
('File at UCSC', 'number'),
('File Raw Type', 'number'),
('Sample Replicate', 'number'),
('Sample Id', 'string'),
('Sample Internal Name', 'string'),
('Replicate Lab', 'string'),
('Replicate Read Type', 'string'),
('Replicate Insert Length', 'string'),
('Replicate Tech Replicate', 'number'),
('Replicate Id', 'string'),
('File Type', 'string'),
('File View', 'string'),
('File Lab', 'string'),
('File URL', 'string'),
('File Size', 'string'),
('File View de novo', 'number'),
('Restricted until', 'string'),
]
chart = {}
chart['table_description'] = description
hgversion = confs['configurations'][0]['hgversion']
dashboard_db = get_dashboard_db(dbs, hgversion)
sql = """
SELECT sample.grantName,
cell.displayName as cellName,
sample.cell,
cell.tier,
localization.displayName as localization,
localization.ucscName,
rnaExtract.displayName as rnaExtract,
rnaExtract.ucscName,
technology.displayName as technology,
technology.name,
file.atUcsc,
fileType.rawType,
sample.replicate as bioRep,
sample.id as sample,
sample.internalName,
experiment.lab as expLab,
experiment.readType,
experiment.insertLength,
experiment.techReplicate as techRep,
experiment.id as expId,
file.fileType,
fileView.displayName,
file.lab as endLab,
file.url,
file.size,
fileView.deNovo,
file.dateSubmitted
FROM sample,
technology,
file,
experiment,
fileType,
localization,
rnaExtract,
cell,
fileView
WHERE
fileType != 'BAI'
AND
file.experiment_data_processing = experiment.id
AND
file.fileType = fileType.name
AND
file.fileView = fileView.name
AND
experiment.sampleName = sample.id
AND
experiment.technology = technology.name
AND
sample.localization = localization.ucscName
AND
sample.rnaExtract = rnaExtract.ucscName
AND
sample.cell = cell.ucscName"""
cursor = dashboard_db.query(sql)
rows = cursor.fetchall()
cursor.close()
results = []
for row in rows:
result = list(row)
if row[10] == 1:
if not result[-1] is None:
end = result[-1] + datetime.timedelta(9 * 365 / 12)
if end > datetime.date.today():
result[-1] = "%s-%s-%s" % (end.year, end.month, end.day)
else:
result[-1] = None
else:
result[-1] = 'To be decided'
results.append(result)
chart['table_data'] = results
return chart