示例#1
0
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
示例#2
0
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
示例#3
0
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
示例#4
0
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
示例#5
0
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
示例#6
0
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
示例#7
0
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
示例#8
0
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
示例#9
0
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
示例#10
0
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
示例#11
0
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
示例#12
0
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