<commit_before>import sqlalchemy
from sqlalchemy import distinct
from sqlalchemy.orm import aliased

from rdr_service.config import GENOME_TYPE_ARRAY, GENOME_TYPE_WGS
from rdr_service.genomic_enums import GenomicSubProcessResult, GenomicWorkflowState, GenomicManifestTypes, \
    GenomicContaminationCategory
from rdr_service.model.config_utils import get_biobank_id_prefix
from rdr_service.model.genomics import GenomicGCValidationMetrics, GenomicSetMember, GenomicSet, GenomicFileProcessed
from rdr_service.model.participant import Participant
from rdr_service.model.participant_summary import ParticipantSummary
from rdr_service.participant_enums import WithdrawalStatus, SuspensionStatus, QuestionnaireStatus


class GenomicQueryClass:

    def __init__(self, input_manifest=None):
        self.input_manifest = input_manifest

        # Table aliases for tables requiring multiple JOINs
        self.aliases = {
            'gsm': aliased(GenomicSetMember),
        }

        self.subqueries = {
            'aw3_wgs_parent_sample_id': (
                sqlalchemy.select([GenomicSetMember.gcManifestParentSampleId]).select_from(GenomicSetMember).where(
                    (GenomicSetMember.genomeType == "aou_array") &
                    (GenomicSetMember.aw3ManifestJobRunID.isnot(None))
                )
            )
        }

        self.genomic_data_config = {
            GenomicManifestTypes.AW3_ARRAY: (sqlalchemy.select(
                [
                    distinct(GenomicGCValidationMetrics.chipwellbarcode),
                    sqlalchemy.func.concat(get_biobank_id_prefix(), GenomicSetMember.biobankId),
                    GenomicSetMember.sampleId,
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.gcSiteId,
                    GenomicGCValidationMetrics.idatRedPath,
                    GenomicGCValidationMetrics.idatRedMd5Path,
                    GenomicGCValidationMetrics.idatGreenPath,
                    GenomicGCValidationMetrics.idatGreenMd5Path,
                    GenomicGCValidationMetrics.vcfPath,
                    GenomicGCValidationMetrics.vcfTbiPath,
                    GenomicGCValidationMetrics.vcfMd5Path,
                    GenomicGCValidationMetrics.callRate,
                    GenomicGCValidationMetrics.sexConcordance,
                    GenomicGCValidationMetrics.contamination,
                    GenomicGCValidationMetrics.processingStatus,
                    Participant.researchId,
                ]
            ).select_from(
                sqlalchemy.join(
                    sqlalchemy.join(
                        sqlalchemy.join(ParticipantSummary,
                                        GenomicSetMember,
                                        GenomicSetMember.participantId == ParticipantSummary.participantId),
                        GenomicGCValidationMetrics,
                        GenomicGCValidationMetrics.genomicSetMemberId == GenomicSetMember.id
                    ),
                    Participant,
                    Participant.participantId == ParticipantSummary.participantId
                )
            ).where(
                (GenomicGCValidationMetrics.processingStatus == 'pass') &
                (GenomicGCValidationMetrics.ignoreFlag != 1) &
                (GenomicSetMember.genomicWorkflowState != GenomicWorkflowState.IGNORE) &
                (GenomicSetMember.genomeType == GENOME_TYPE_ARRAY) &
                (ParticipantSummary.withdrawalStatus == WithdrawalStatus.NOT_WITHDRAWN) &
                (ParticipantSummary.suspensionStatus == SuspensionStatus.NOT_SUSPENDED) &
                (GenomicGCValidationMetrics.idatRedReceived == 1) &
                (GenomicGCValidationMetrics.idatGreenReceived == 1) &
                (GenomicGCValidationMetrics.idatRedMd5Received == 1) &
                (GenomicGCValidationMetrics.idatGreenMd5Received == 1) &
                (GenomicGCValidationMetrics.vcfReceived == 1) &
                (GenomicGCValidationMetrics.vcfMd5Received == 1) &
                (GenomicSetMember.aw3ManifestJobRunID.is_(None))
            )),
            GenomicManifestTypes.AW3_WGS: (sqlalchemy.select(
                [
                    distinct(sqlalchemy.func.concat(get_biobank_id_prefix(), GenomicSetMember.biobankId)),
                    GenomicSetMember.sampleId,
                    sqlalchemy.func.concat(get_biobank_id_prefix(),
                                           GenomicSetMember.biobankId, '_',
                                           GenomicSetMember.sampleId),
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.gcSiteId,
                    GenomicGCValidationMetrics.hfVcfPath,
                    GenomicGCValidationMetrics.hfVcfTbiPath,
                    GenomicGCValidationMetrics.hfVcfMd5Path,
                    GenomicGCValidationMetrics.rawVcfPath,
                    GenomicGCValidationMetrics.rawVcfTbiPath,
                    GenomicGCValidationMetrics.rawVcfMd5Path,
                    GenomicGCValidationMetrics.cramPath,
                    GenomicGCValidationMetrics.cramMd5Path,
                    GenomicGCValidationMetrics.craiPath,
                    GenomicGCValidationMetrics.gvcfPath,
                    GenomicGCValidationMetrics.gvcfMd5Path,
                    GenomicGCValidationMetrics.contamination,
                    GenomicGCValidationMetrics.sexConcordance,
                    GenomicGCValidationMetrics.processingStatus,
                    GenomicGCValidationMetrics.meanCoverage,
                    Participant.researchId,
                    GenomicSetMember.sampleId,
                ]
            ).select_from(
                sqlalchemy.join(
                    ParticipantSummary,
                    GenomicSetMember,
                    GenomicSetMember.participantId == ParticipantSummary.participantId
                ).join(
                    GenomicGCValidationMetrics,
                    GenomicGCValidationMetrics.genomicSetMemberId == GenomicSetMember.id
                ).join(
                    Participant,
                    Participant.participantId == ParticipantSummary.participantId
                )
            ).where(
                (GenomicGCValidationMetrics.processingStatus == 'pass') &
                (GenomicGCValidationMetrics.ignoreFlag != 1) &
                (GenomicSetMember.genomicWorkflowState != GenomicWorkflowState.IGNORE) &
                (GenomicSetMember.genomeType == GENOME_TYPE_WGS) &
                (ParticipantSummary.withdrawalStatus == WithdrawalStatus.NOT_WITHDRAWN) &
                (ParticipantSummary.suspensionStatus == SuspensionStatus.NOT_SUSPENDED) &
                (GenomicSetMember.aw3ManifestJobRunID.is_(None)) &
                (GenomicGCValidationMetrics.hfVcfReceived == 1) &
                (GenomicGCValidationMetrics.hfVcfTbiReceived == 1) &
                (GenomicGCValidationMetrics.hfVcfMd5Received == 1) &
                (GenomicGCValidationMetrics.cramReceived == 1) &
                (GenomicGCValidationMetrics.cramMd5Received == 1) &
                (GenomicGCValidationMetrics.craiReceived == 1) &
                (GenomicSetMember.gcManifestParentSampleId.in_(self.subqueries['aw3_wgs_parent_sample_id']))
            )),
            GenomicManifestTypes.CVL_W1: (sqlalchemy.select(
                [
                    GenomicSet.genomicSetName,
                    GenomicSetMember.biobankId,
                    GenomicSetMember.sampleId,
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.nyFlag,
                    GenomicGCValidationMetrics.siteId,<commit_msg>need to require gvcf on aw3 wgs (#2576)<commit_after>import sqlalchemy
from sqlalchemy import distinct
from sqlalchemy.orm import aliased

from rdr_service.config import GENOME_TYPE_ARRAY, GENOME_TYPE_WGS
from rdr_service.genomic_enums import GenomicSubProcessResult, GenomicWorkflowState, GenomicManifestTypes, \
    GenomicContaminationCategory
from rdr_service.model.config_utils import get_biobank_id_prefix
from rdr_service.model.genomics import GenomicGCValidationMetrics, GenomicSetMember, GenomicSet, GenomicFileProcessed
from rdr_service.model.participant import Participant
from rdr_service.model.participant_summary import ParticipantSummary
from rdr_service.participant_enums import WithdrawalStatus, SuspensionStatus, QuestionnaireStatus


class GenomicQueryClass:

    def __init__(self, input_manifest=None):
        self.input_manifest = input_manifest

        # Table aliases for tables requiring multiple JOINs
        self.aliases = {
            'gsm': aliased(GenomicSetMember),
        }

        self.subqueries = {
            'aw3_wgs_parent_sample_id': (
                sqlalchemy.select([GenomicSetMember.gcManifestParentSampleId]).select_from(GenomicSetMember).where(
                    (GenomicSetMember.genomeType == "aou_array") &
                    (GenomicSetMember.aw3ManifestJobRunID.isnot(None))
                )
            )
        }

        self.genomic_data_config = {
            GenomicManifestTypes.AW3_ARRAY: (sqlalchemy.select(
                [
                    distinct(GenomicGCValidationMetrics.chipwellbarcode),
                    sqlalchemy.func.concat(get_biobank_id_prefix(), GenomicSetMember.biobankId),
                    GenomicSetMember.sampleId,
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.gcSiteId,
                    GenomicGCValidationMetrics.idatRedPath,
                    GenomicGCValidationMetrics.idatRedMd5Path,
                    GenomicGCValidationMetrics.idatGreenPath,
                    GenomicGCValidationMetrics.idatGreenMd5Path,
                    GenomicGCValidationMetrics.vcfPath,
                    GenomicGCValidationMetrics.vcfTbiPath,
                    GenomicGCValidationMetrics.vcfMd5Path,
                    GenomicGCValidationMetrics.callRate,
                    GenomicGCValidationMetrics.sexConcordance,
                    GenomicGCValidationMetrics.contamination,
                    GenomicGCValidationMetrics.processingStatus,
                    Participant.researchId,
                ]
            ).select_from(
                sqlalchemy.join(
                    sqlalchemy.join(
                        sqlalchemy.join(ParticipantSummary,
                                        GenomicSetMember,
                                        GenomicSetMember.participantId == ParticipantSummary.participantId),
                        GenomicGCValidationMetrics,
                        GenomicGCValidationMetrics.genomicSetMemberId == GenomicSetMember.id
                    ),
                    Participant,
                    Participant.participantId == ParticipantSummary.participantId
                )
            ).where(
                (GenomicGCValidationMetrics.processingStatus == 'pass') &
                (GenomicGCValidationMetrics.ignoreFlag != 1) &
                (GenomicSetMember.genomicWorkflowState != GenomicWorkflowState.IGNORE) &
                (GenomicSetMember.genomeType == GENOME_TYPE_ARRAY) &
                (ParticipantSummary.withdrawalStatus == WithdrawalStatus.NOT_WITHDRAWN) &
                (ParticipantSummary.suspensionStatus == SuspensionStatus.NOT_SUSPENDED) &
                (GenomicGCValidationMetrics.idatRedReceived == 1) &
                (GenomicGCValidationMetrics.idatGreenReceived == 1) &
                (GenomicGCValidationMetrics.idatRedMd5Received == 1) &
                (GenomicGCValidationMetrics.idatGreenMd5Received == 1) &
                (GenomicGCValidationMetrics.vcfReceived == 1) &
                (GenomicGCValidationMetrics.vcfMd5Received == 1) &
                (GenomicSetMember.aw3ManifestJobRunID.is_(None))
            )),
            GenomicManifestTypes.AW3_WGS: (sqlalchemy.select(
                [
                    distinct(sqlalchemy.func.concat(get_biobank_id_prefix(), GenomicSetMember.biobankId)),
                    GenomicSetMember.sampleId,
                    sqlalchemy.func.concat(get_biobank_id_prefix(),
                                           GenomicSetMember.biobankId, '_',
                                           GenomicSetMember.sampleId),
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.gcSiteId,
                    GenomicGCValidationMetrics.hfVcfPath,
                    GenomicGCValidationMetrics.hfVcfTbiPath,
                    GenomicGCValidationMetrics.hfVcfMd5Path,
                    GenomicGCValidationMetrics.rawVcfPath,
                    GenomicGCValidationMetrics.rawVcfTbiPath,
                    GenomicGCValidationMetrics.rawVcfMd5Path,
                    GenomicGCValidationMetrics.cramPath,
                    GenomicGCValidationMetrics.cramMd5Path,
                    GenomicGCValidationMetrics.craiPath,
                    GenomicGCValidationMetrics.gvcfPath,
                    GenomicGCValidationMetrics.gvcfMd5Path,
                    GenomicGCValidationMetrics.contamination,
                    GenomicGCValidationMetrics.sexConcordance,
                    GenomicGCValidationMetrics.processingStatus,
                    GenomicGCValidationMetrics.meanCoverage,
                    Participant.researchId,
                    GenomicSetMember.sampleId,
                ]
            ).select_from(
                sqlalchemy.join(
                    ParticipantSummary,
                    GenomicSetMember,
                    GenomicSetMember.participantId == ParticipantSummary.participantId
                ).join(
                    GenomicGCValidationMetrics,
                    GenomicGCValidationMetrics.genomicSetMemberId == GenomicSetMember.id
                ).join(
                    Participant,
                    Participant.participantId == ParticipantSummary.participantId
                )
            ).where(
                (GenomicGCValidationMetrics.processingStatus == 'pass') &
                (GenomicGCValidationMetrics.ignoreFlag != 1) &
                (GenomicSetMember.genomicWorkflowState != GenomicWorkflowState.IGNORE) &
                (GenomicSetMember.genomeType == GENOME_TYPE_WGS) &
                (ParticipantSummary.withdrawalStatus == WithdrawalStatus.NOT_WITHDRAWN) &
                (ParticipantSummary.suspensionStatus == SuspensionStatus.NOT_SUSPENDED) &
                (GenomicSetMember.aw3ManifestJobRunID.is_(None)) &
                (GenomicGCValidationMetrics.hfVcfReceived == 1) &
                (GenomicGCValidationMetrics.hfVcfTbiReceived == 1) &
                (GenomicGCValidationMetrics.hfVcfMd5Received == 1) &
                (GenomicGCValidationMetrics.cramReceived == 1) &
                (GenomicGCValidationMetrics.cramMd5Received == 1) &
                (GenomicGCValidationMetrics.craiReceived == 1) &
                (GenomicGCValidationMetrics.gvcfReceived == 1) &
                (GenomicGCValidationMetrics.gvcfMd5Received == 1) &
                (GenomicSetMember.gcManifestParentSampleId.in_(self.subqueries['aw3_wgs_parent_sample_id']))
            )),
            GenomicManifestTypes.CVL_W1: (sqlalchemy.select(
                [
                    GenomicSet.genomicSetName,
                    GenomicSetMember.biobankId,
                    GenomicSetMember.sampleId,
                    GenomicSetMember.sexAtBirth,
                    GenomicSetMember.nyFlag,
                    GenomicGCValidationMetrics.siteId,
{'fraction_non_alphanumeric': 0.061434544869733036, 'fraction_numerical': 0.004889031843036346, 'mean_word_length': 1.8725055432372506, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7373193', 'n_tokens_mistral': 4127, 'n_tokens_neox': 3788, 'n_words': 422}