<commit_before>import tornado.web
import json
import base64
import requests

from status.util import SafeHandler
from genologics.config import BASEURI, USERNAME, PASSWORD
from genologics import lims
from genologics.entities import Queue, Artifact, Container
import xml.etree.ElementTree as ET
import psycopg2
from dateutil.parser import parse
import ast

control_names = [ 'AM7852', 'E.Coli genDNA', 'Endogenous Positive Control', 'Exogenous Positive Control',
                    'Human Brain Reference RNA', 'lambda DNA', 'mQ Negative Control', 'NA10860', 'NA11992',
                    'NA11993', 'NA12878', 'NA12891', 'NA12892', 'No Amplification Control',
                    'No Reverse Transcriptase Control', 'No Template Control', 'PhiX v3', 'Universal Human Reference RNA',
                    'lambda DNA (qPCR)'
                 ]

class qPCRPoolsDataHandler(SafeHandler):
    """ Serves a page with qPCR queues from LIMS listed
    URL: /api/v1/qpcr_pools
    """
    def get(self):

        unwanted_in_lib_val = [ "Illumina TruSeq PCR-free", "Illumina DNA PCR-free FLEX", "SMARTer ThruPLEX DNA-seq (small genome)",
                                "SMARTer ThruPLEX DNA-seq (complex genome, metagenomes)", "SMARTer ThruPLEX DNA-seq (ChIP)",
                                "Illumina TruSeq Stranded mRNA (polyA)", "Illumina TruSeq Stranded total RNA",
                                "SMARTer Total Stranded RNA-seq, Pico input mammalian", "SMARTer Total Stranded RNA-seq, Pico input mammalian - V3",
                                "Illumina TruSeq small RNA", "QIAseq miRNA low input", "Visium Spatial Gene Expression", "RAD-seq", "ATAC-seq",
                                "Dovetail OmniC",  "Dovetail MicroC", "Arima HiC (standard)", "Arima HiC (low input)", "16S",
                                "Amplicon indexing (without cleanup)", "Amplicon indexing (with cleanup)", "Special", "ONT ligation - DNA",
                                "ONT PCR - DNA", "ONT cDNA PCR - RNA", "ONT direct cDNA - RNA", "ONT direct RNA - RNA"]

        queues = {}
        #query for Miseq and NovaSeq
        query = ('select art.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid '
                    'from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm '
                    'where art.artifactid=st.artifactid and st.stageid in (select stageid from stage where stepid={}) and st.completedbyid is null and st.workflowrunid>0 '
                    'and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and s.processid=asm.processid and asm.artifactid=art.artifactid '
                    'group by art.artifactid, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid;')
        #Queue = 1002, stepid in the query
        queues['MiSeq'] = query.format(1002)
        #Queue = 1666, stepid in the query
        queues['NovaSeq'] = query.format(1666)
        #Queue = 2102, stepid in the query
        queues['NextSeq'] = query.format(2102)
        #Queue 41, but query is slightly different to use protocolid for Library Validation QC which is 8 and, also to exclude the controls
        queues['LibraryValidation'] = ("select  st.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid, e.udfvalue "
                                        "from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm, entity_udf_view e where "
                                        "art.artifactid=st.artifactid and st.stageid in (select stageid from stage where membershipid in (select sectionid from workflowsection where protocolid=8)) "
                                        "and st.workflowrunid>0 and st.completedbyid is null and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and s.processid=asm.processid "
                                        "and asm.artifactid=art.artifactid and art.name not in {} and s.projectid=e.attachtoid and e.udfname=\'Library construction method\'"
                                        "group by st.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid, e.udfvalue;".format(tuple(control_names)))

        methods = queues.keys()
        projects = self.application.projects_db.view("project/project_id")
        connection = psycopg2.connect(user=self.application.lims_conf['username'], host=self.application.lims_conf['url'],
                                        database=self.application.lims_conf['db'], password=self.application.lims_conf['password'])
        cursor = connection.cursor()
        pools = {}
        for method in methods:
            pools[method] = {}
            query = queues[method]
            cursor.execute(query)
            records = cursor.fetchall()
            for record in list(records):
                queue_time = record[2].isoformat()
                container = record[4]
                value = chr(65+int(record[6])) + ':' + str(int(record[5])+1)
                project = 'P'+str(record[7])
                library_type = ''
                sequencing_platform = ''
                flowcell = ''
                queued_date = ''

                if container in pools[method]:
                    pools[method][container]['samples'].append({'name': record[1], 'well': value, 'queue_time': queue_time})
                    if project not in pools[method][container]['projects']:
                        if method =='LibraryValidation' and record[8] in unwanted_in_lib_val:
                            pools[method][container]['samples'].pop()
                            continue
                        proj_doc = self.application.projects_db.get(projects[project].rows[0].value)
                        library_type =  proj_doc['details'].get('library_construction_method', '')
                        sequencing_platform = proj_doc['details'].get('sequencing_platform', '')
                        flowcell = proj_doc['details'].get('flowcell', '')
                        queued_date = proj_doc['details'].get('queued', '')
                        if not queued_date:
                            queued_date = proj_doc.get('project_summary', {}).get('queued', '')
                        if library_type not in pools[method][container]['library_types']:
                            pools[method][container]['library_types'].append(library_type)
                        if sequencing_platform not in pools[method][container]['sequencing_platforms']:
                            pools[method][container]['sequencing_platforms'].append(sequencing_platform)
                        if flowcell not in pools[method][container]['flowcells']:
                            pools[method][container]['flowcells'].append(flowcell)
                        pools[method][container]['proj_queue_dates'].append(queued_date)
                        pools[method][container]['projects'][project] = proj_doc['project_name']
                else:
                    if method =='LibraryValidation' and record[8] in unwanted_in_lib_val:
                        continue
                    proj_doc = self.application.projects_db.get(projects[project].rows[0].value)
                    library_type =  proj_doc['details'].get('library_construction_method', '')
                    sequencing_platform = proj_doc['details'].get('sequencing_platform', '')
                    flowcell = proj_doc['details'].get('flowcell', '')
                    queued_date = proj_doc['details'].get('queued', '')
                    if not queued_date:
                        queued_date = proj_doc.get('project_summary', {}).get('queued', '')
                    pools[method][container] = {
                                                'samples':[{'name': record[1], 'well': value, 'queue_time': queue_time}],
                                                'library_types': [library_type],
                                                'sequencing_platforms': [sequencing_platform],
                                                'flowcells': [flowcell],
                                                'proj_queue_dates': [queued_date],
                                                'projects': {project: proj_doc['project_name']}
                                                }

        self.set_header("Content-type", "application/json")
        self.write(json.dumps(pools))


class qPCRPoolsHandler(SafeHandler):
    """ Serves a page with qPCR queues from LIMS listed
    URL: /pools_qpcr
    """
    def get(self):
        t = self.application.loader.load("qpcr_pools.html")
        self.write(t.generate(gs_globals=self.application.gs_globals, user=self.get_current_user()))


class SequencingQueuesDataHandler(SafeHandler):
    """ Serves a page with sequencing queues from LIMS listed
    URL: /api/v1/sequencing_queues
    """

    def get(self):

        query = ('select art.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, s.projectid '
                    'from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm '
                    'where art.artifactid=st.artifactid and st.stageid in (select stageid from stage where stepid={}) and st.completedbyid is null and '
                    'st.workflowrunid>0 and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and  s.processid=asm.processid and '
                    'asm.artifactid=art.artifactid group by art.artifactid, st.lastmodifieddate, st.generatedbyid, ct.name, s.projectid;')

        #works for 2109, 1659
        #does not work for 1662, 1655, 1656
        pool_conc_query = ('select udfvalue from artifact_udf_view where udfname=\'{}\' '
                            'and artifactid in (select art.artifactid from artifact_sample_map asm, artifact art '
                            'where processid=(select processid from artifact_sample_map where artifactid={} limit 1) '
                            'and art.artifactid=asm.artifactid and art.name=\'{}\');')

        qpcr_conc_query =  ('select aus.numeric0 from artifactudfstorage aus, artifact_ancestor_map aam, artifact art '
                             'where aam.ancestorartifactid={} and aus.artifactid=aam.artifactid and aus.artifactid=art.artifactid '
                             'and art.name=\'qPCR Measurement\' and aus.text0=\'nM\';')
        #1662
        pool_conc_query_nextseq = ('select udfvalue from artifact_udf_view where udfname=\'{}\' '
                                    'and artifactid=(select artifactid from artifactstate '
                                    'where stateid=(select inputstatepostid from processiotracker where processid={} limit 1));')
        rerun_query = ('select count(artifactid) from stagetransition '
                        'where stageid in (select stageid from stage where stepid={}) '
                        'and artifactid={} group by artifactid')

        #sequencing queues are currently taken as the following
        queues = {}
        #Miseq- Step 7: Denature, Dilute and load sample
        queues['MiSeq: Denature, Dilute and Load Sample'] = '55'
        #NextSeq- Step 7: Load to Flowcell
        queues['NextSeq: Load to Flowcell'] = '2109'
        #Novaseq Step 11: Load to flow cell
        queues['NovaSeq: Load to Flowcell'] = '1662'
        #Novaseq Step 7: Define Run Format
        queues['NovaSeq: Define Run Format'] = '1659'
        #Novaseq Step 8: Make Bulk Pool for Novaseq Standard
        queues['NovaSeq: Make Bulk Pool for Standard'] = '1655'<commit_msg>Add check for control types in sequencing queues<commit_after>import tornado.web
import json
import base64
import requests

from status.util import SafeHandler
from genologics.config import BASEURI, USERNAME, PASSWORD
from genologics import lims
from genologics.entities import Queue, Artifact, Container
import xml.etree.ElementTree as ET
import psycopg2
from dateutil.parser import parse
import ast

#Control names can be found in the table controltype in the lims backend. Will continue to check against this list for now, should
#consider incorporating a check against this table directly into the queries in the future
control_names = [ 'AM7852', 'E.Coli genDNA', 'Endogenous Positive Control', 'Exogenous Positive Control',
                    'Human Brain Reference RNA', 'lambda DNA', 'mQ Negative Control', 'NA10860', 'NA11992',
                    'NA11993', 'NA12878', 'NA12891', 'NA12892', 'No Amplification Control',
                    'No Reverse Transcriptase Control', 'No Template Control', 'PhiX v3', 'Universal Human Reference RNA',
                    'lambda DNA (qPCR)'
                 ]

class qPCRPoolsDataHandler(SafeHandler):
    """ Serves a page with qPCR queues from LIMS listed
    URL: /api/v1/qpcr_pools
    """
    def get(self):

        unwanted_in_lib_val = [ "Illumina TruSeq PCR-free", "Illumina DNA PCR-free FLEX", "SMARTer ThruPLEX DNA-seq (small genome)",
                                "SMARTer ThruPLEX DNA-seq (complex genome, metagenomes)", "SMARTer ThruPLEX DNA-seq (ChIP)",
                                "Illumina TruSeq Stranded mRNA (polyA)", "Illumina TruSeq Stranded total RNA",
                                "SMARTer Total Stranded RNA-seq, Pico input mammalian", "SMARTer Total Stranded RNA-seq, Pico input mammalian - V3",
                                "Illumina TruSeq small RNA", "QIAseq miRNA low input", "Visium Spatial Gene Expression", "RAD-seq", "ATAC-seq",
                                "Dovetail OmniC",  "Dovetail MicroC", "Arima HiC (standard)", "Arima HiC (low input)", "16S",
                                "Amplicon indexing (without cleanup)", "Amplicon indexing (with cleanup)", "Special", "ONT ligation - DNA",
                                "ONT PCR - DNA", "ONT cDNA PCR - RNA", "ONT direct cDNA - RNA", "ONT direct RNA - RNA"]

        queues = {}
        #query for Miseq and NovaSeq
        query = ('select art.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid '
                    'from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm '
                    'where art.artifactid=st.artifactid and st.stageid in (select stageid from stage where stepid={}) and st.completedbyid is null and st.workflowrunid>0 '
                    'and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and s.processid=asm.processid and asm.artifactid=art.artifactid '
                    'group by art.artifactid, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid;')
        #Queue = 1002, stepid in the query
        queues['MiSeq'] = query.format(1002)
        #Queue = 1666, stepid in the query
        queues['NovaSeq'] = query.format(1666)
        #Queue = 2102, stepid in the query
        queues['NextSeq'] = query.format(2102)
        #Queue 41, but query is slightly different to use protocolid for Library Validation QC which is 8 and, also to exclude the controls
        queues['LibraryValidation'] = ("select  st.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid, e.udfvalue "
                                        "from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm, entity_udf_view e where "
                                        "art.artifactid=st.artifactid and st.stageid in (select stageid from stage where membershipid in (select sectionid from workflowsection where protocolid=8)) "
                                        "and st.workflowrunid>0 and st.completedbyid is null and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and s.processid=asm.processid "
                                        "and asm.artifactid=art.artifactid and art.name not in {} and s.projectid=e.attachtoid and e.udfname=\'Library construction method\'"
                                        "group by st.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, ctp.wellxposition, ctp.wellyposition, s.projectid, e.udfvalue;".format(tuple(control_names)))

        methods = queues.keys()
        projects = self.application.projects_db.view("project/project_id")
        connection = psycopg2.connect(user=self.application.lims_conf['username'], host=self.application.lims_conf['url'],
                                        database=self.application.lims_conf['db'], password=self.application.lims_conf['password'])
        cursor = connection.cursor()
        pools = {}
        for method in methods:
            pools[method] = {}
            query = queues[method]
            cursor.execute(query)
            records = cursor.fetchall()
            for record in list(records):
                queue_time = record[2].isoformat()
                container = record[4]
                value = chr(65+int(record[6])) + ':' + str(int(record[5])+1)
                project = 'P'+str(record[7])
                library_type = ''
                sequencing_platform = ''
                flowcell = ''
                queued_date = ''

                if container in pools[method]:
                    pools[method][container]['samples'].append({'name': record[1], 'well': value, 'queue_time': queue_time})
                    if project not in pools[method][container]['projects']:
                        if method =='LibraryValidation' and record[8] in unwanted_in_lib_val:
                            pools[method][container]['samples'].pop()
                            continue
                        proj_doc = self.application.projects_db.get(projects[project].rows[0].value)
                        library_type =  proj_doc['details'].get('library_construction_method', '')
                        sequencing_platform = proj_doc['details'].get('sequencing_platform', '')
                        flowcell = proj_doc['details'].get('flowcell', '')
                        queued_date = proj_doc['details'].get('queued', '')
                        if not queued_date:
                            queued_date = proj_doc.get('project_summary', {}).get('queued', '')
                        if library_type not in pools[method][container]['library_types']:
                            pools[method][container]['library_types'].append(library_type)
                        if sequencing_platform not in pools[method][container]['sequencing_platforms']:
                            pools[method][container]['sequencing_platforms'].append(sequencing_platform)
                        if flowcell not in pools[method][container]['flowcells']:
                            pools[method][container]['flowcells'].append(flowcell)
                        pools[method][container]['proj_queue_dates'].append(queued_date)
                        pools[method][container]['projects'][project] = proj_doc['project_name']
                else:
                    if method =='LibraryValidation' and record[8] in unwanted_in_lib_val:
                        continue
                    proj_doc = self.application.projects_db.get(projects[project].rows[0].value)
                    library_type =  proj_doc['details'].get('library_construction_method', '')
                    sequencing_platform = proj_doc['details'].get('sequencing_platform', '')
                    flowcell = proj_doc['details'].get('flowcell', '')
                    queued_date = proj_doc['details'].get('queued', '')
                    if not queued_date:
                        queued_date = proj_doc.get('project_summary', {}).get('queued', '')
                    pools[method][container] = {
                                                'samples':[{'name': record[1], 'well': value, 'queue_time': queue_time}],
                                                'library_types': [library_type],
                                                'sequencing_platforms': [sequencing_platform],
                                                'flowcells': [flowcell],
                                                'proj_queue_dates': [queued_date],
                                                'projects': {project: proj_doc['project_name']}
                                                }

        self.set_header("Content-type", "application/json")
        self.write(json.dumps(pools))


class qPCRPoolsHandler(SafeHandler):
    """ Serves a page with qPCR queues from LIMS listed
    URL: /pools_qpcr
    """
    def get(self):
        t = self.application.loader.load("qpcr_pools.html")
        self.write(t.generate(gs_globals=self.application.gs_globals, user=self.get_current_user()))


class SequencingQueuesDataHandler(SafeHandler):
    """ Serves a page with sequencing queues from LIMS listed
    URL: /api/v1/sequencing_queues
    """

    def get(self):

        query = ('select art.artifactid, art.name, st.lastmodifieddate, st.generatedbyid, ct.name, s.projectid '
                    'from artifact art, stagetransition st, container ct, containerplacement ctp, sample s, artifact_sample_map asm '
                    'where art.artifactid=st.artifactid and st.stageid in (select stageid from stage where stepid={}) and st.completedbyid is null and '
                    'st.workflowrunid>0 and ctp.processartifactid=st.artifactid and ctp.containerid=ct.containerid and  s.processid=asm.processid and '
                    'asm.artifactid=art.artifactid and s.name not in (select name from controltype) '
                    'group by art.artifactid, st.lastmodifieddate, st.generatedbyid, ct.name, s.projectid;')

        #works for 2109, 1659
        #does not work for 1662, 1655, 1656
        pool_conc_query = ('select udfvalue from artifact_udf_view where udfname=\'{}\' '
                            'and artifactid in (select art.artifactid from artifact_sample_map asm, artifact art '
                            'where processid=(select processid from artifact_sample_map where artifactid={} limit 1) '
                            'and art.artifactid=asm.artifactid and art.name=\'{}\');')

        qpcr_conc_query =  ('select aus.numeric0 from artifactudfstorage aus, artifact_ancestor_map aam, artifact art '
                             'where aam.ancestorartifactid={} and aus.artifactid=aam.artifactid and aus.artifactid=art.artifactid '
                             'and art.name=\'qPCR Measurement\' and aus.text0=\'nM\';')
        #1662
        pool_conc_query_nextseq = ('select udfvalue from artifact_udf_view where udfname=\'{}\' '
                                    'and artifactid=(select artifactid from artifactstate '
                                    'where stateid=(select inputstatepostid from processiotracker where processid={} limit 1));')
        rerun_query = ('select count(artifactid) from stagetransition '
                        'where stageid in (select stageid from stage where stepid={}) '
                        'and artifactid={} group by artifactid')

        #sequencing queues are currently taken as the following
        queues = {}
        #Miseq- Step 7: Denature, Dilute and load sample
        queues['MiSeq: Denature, Dilute and Load Sample'] = '55'
        #NextSeq- Step 7: Load to Flowcell
        queues['NextSeq: Load to Flowcell'] = '2109'
        #Novaseq Step 11: Load to flow cell
        queues['NovaSeq: Load to Flowcell'] = '1662'
        #Novaseq Step 7: Define Run Format
        queues['NovaSeq: Define Run Format'] = '1659'
        #Novaseq Step 8: Make Bulk Pool for Novaseq Standard
        queues['NovaSeq: Make Bulk Pool for Standard'] = '1655'
{'fraction_non_alphanumeric': 0.10339654094603043, 'fraction_numerical': 0.011752448426755574, 'mean_word_length': 2.1523909616395165, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 50, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2436179', 'n_tokens_mistral': 6879, 'n_tokens_neox': 6252, 'n_words': 1649}