<commit_before>'''
Created on Mar 27, 2015

Copyright 2015, Institute for Systems Biology.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

@author: michael

json return layout
{
    "dccAnnotation":[ {
        "id":26882,
        "dateCreated":"2015-03-06T14:42:56-05:00",
        "createdBy":"LeraasK",
        "status":"Approved",
        "annotationCategory": {
            "categoryId":25,
            "categoryName":"Item may not meet study protocol",
            "annotationClassification": {
                "annotationClassificationId":1,
                "annotationClassificationName":"Observation"
            }
        },
        "items":[ {
            "item":"TCGA-HT-7483",
            "uuid":"183dd089-e932-4be2-b252-0e8572e7da4e",
            "itemType": {
                "itemTypeId":3,
                "itemTypeName":"Patient"
            },
            "disease": {
                "diseaseId":21,
                "abbreviation":"LGG",
                "description":"Brain Lower Grade Glioma"
            },
            "id":26338
        } ],
        "notes":[ {
            "noteId":26427,
            "noteText":"TSS confirmed that submitted tumor is a recurrence and patient had 2 prior resections before tumor submitted to BCR. Submitted tumor was in same tumor bed as primary. The patient had no prior chemo/radiation treatment. ",
            "addedBy":"LeraasK",
            "dateAdded":"2015-03-06T14:42:56-05:00"
            "dateEdited":"2015-03-07T14:42:56-05:00"
        } ],
        "approved":true,
        "rescinded":false
    }
    ...
    ]
}

Annotation Classification    Id
Observation    1
CenterNotification    2
Notification    3
Redaction    5

Annotation Category    Id
annotationClassification    annotationCategoryName    annotationCategoryId
CenterNotification:Center QC failed    28
CenterNotification:Item Flagged Low Quality    465
CenterNotification:Item flagged DNU    29
Notification:Acceptable treatment for TCGA tumor    355
Notification:Alternate sample pipeline    205
Notification    BCR Notification:    38
Notification:Barcode incorrect    243
Notification:Case submitted is found to be a recurrence after submission    203
Notification:Duplicate item    11
Notification:History of acceptable prior treatment related to a prior/other malignancy    202
Notification:History of unacceptable prior treatment related to a prior/other malignancy    201
Notification:Item does not meet study protocol    15
Notification:Item in special subset    17
Notification:Item is noncanonical    21
Notification    Molecular analysis outside specification:    10
Notification:Neoadjuvant therapy    7
Notification:Normal tissue origin incorrect    35
Notification:Pathology outside specification    9
Notification:Permanently missing item or object    36
Notification:Prior malignancy    6
Notification    Qualification:metrics changed    8
Notification:Qualified in error    18
Notification:Sample compromised    13
Notification:Synchronous malignancy    204
Notification:WGA Failure    181
Observation:General    30
Observation:Item may not meet study protocol    25
Observation:Normal class but appears diseased    24
Observation:Tumor class but appears normal    23
*Redaction:Administrative Compliance    37
*Redaction:Biospecimen identity unknown    242
*Redaction:Duplicate case    27
*Redaction:Genotype mismatch    3
*Redaction:Inadvertently shipped    241
*Redaction:Subject identity unknown    5
*Redaction:Subject withdrew consent    4
*Redaction:Tumor tissue origin incorrect    1
*Redaction:Tumor type incorrect    2

*indicates do not include
'''
import json
import logging
import pprint
import urllib

import isbcgc_cloudsql_annotation_model
import isbcgc_cloudsql_annotation_association_model
from util import post_run_file

def associate_metadata2annotation(config, log):
    # now save the associations
    associate_statements = [
        "insert into metadata_annotation2data " \
            "(metadata_annotation_id, metadata_data_id) " \
        "select a.metadata_annotation_id, s.metadata_data_id " \
        "from metadata_annotation a join metadata_data s on " \
            "0 < instr(s.aliquotbarcode, a.itembarcode) ",<commit_msg>break the metadata_annotation2data update from INSTR to individaul barcode lengths<commit_after>'''
Created on Mar 27, 2015

Copyright 2015, Institute for Systems Biology.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

@author: michael

json return layout
{
    "dccAnnotation":[ {
        "id":26882,
        "dateCreated":"2015-03-06T14:42:56-05:00",
        "createdBy":"LeraasK",
        "status":"Approved",
        "annotationCategory": {
            "categoryId":25,
            "categoryName":"Item may not meet study protocol",
            "annotationClassification": {
                "annotationClassificationId":1,
                "annotationClassificationName":"Observation"
            }
        },
        "items":[ {
            "item":"TCGA-HT-7483",
            "uuid":"183dd089-e932-4be2-b252-0e8572e7da4e",
            "itemType": {
                "itemTypeId":3,
                "itemTypeName":"Patient"
            },
            "disease": {
                "diseaseId":21,
                "abbreviation":"LGG",
                "description":"Brain Lower Grade Glioma"
            },
            "id":26338
        } ],
        "notes":[ {
            "noteId":26427,
            "noteText":"TSS confirmed that submitted tumor is a recurrence and patient had 2 prior resections before tumor submitted to BCR. Submitted tumor was in same tumor bed as primary. The patient had no prior chemo/radiation treatment. ",
            "addedBy":"LeraasK",
            "dateAdded":"2015-03-06T14:42:56-05:00"
            "dateEdited":"2015-03-07T14:42:56-05:00"
        } ],
        "approved":true,
        "rescinded":false
    }
    ...
    ]
}

Annotation Classification    Id
Observation    1
CenterNotification    2
Notification    3
Redaction    5

Annotation Category    Id
annotationClassification    annotationCategoryName    annotationCategoryId
CenterNotification:Center QC failed    28
CenterNotification:Item Flagged Low Quality    465
CenterNotification:Item flagged DNU    29
Notification:Acceptable treatment for TCGA tumor    355
Notification:Alternate sample pipeline    205
Notification    BCR Notification:    38
Notification:Barcode incorrect    243
Notification:Case submitted is found to be a recurrence after submission    203
Notification:Duplicate item    11
Notification:History of acceptable prior treatment related to a prior/other malignancy    202
Notification:History of unacceptable prior treatment related to a prior/other malignancy    201
Notification:Item does not meet study protocol    15
Notification:Item in special subset    17
Notification:Item is noncanonical    21
Notification    Molecular analysis outside specification:    10
Notification:Neoadjuvant therapy    7
Notification:Normal tissue origin incorrect    35
Notification:Pathology outside specification    9
Notification:Permanently missing item or object    36
Notification:Prior malignancy    6
Notification    Qualification:metrics changed    8
Notification:Qualified in error    18
Notification:Sample compromised    13
Notification:Synchronous malignancy    204
Notification:WGA Failure    181
Observation:General    30
Observation:Item may not meet study protocol    25
Observation:Normal class but appears diseased    24
Observation:Tumor class but appears normal    23
*Redaction:Administrative Compliance    37
*Redaction:Biospecimen identity unknown    242
*Redaction:Duplicate case    27
*Redaction:Genotype mismatch    3
*Redaction:Inadvertently shipped    241
*Redaction:Subject identity unknown    5
*Redaction:Subject withdrew consent    4
*Redaction:Tumor tissue origin incorrect    1
*Redaction:Tumor type incorrect    2

*indicates do not include
'''
import json
import logging
import pprint
import urllib

import isbcgc_cloudsql_annotation_model
import isbcgc_cloudsql_annotation_association_model
from util import post_run_file

def associate_metadata2annotation(config, log):
    # now save the associations
    associate_statements = [
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "s.aliquotbarcode = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 15) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 19) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 20) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 23) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 24) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "left(s.aliquotbarcode, 27) = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "s.samplebarcode = a.itembarcode",
        "insert into metadata_annotation2data" \
            "(metadata_annotation_id, metadata_data_id)" \
        "select a.metadata_annotation_id, s.metadata_data_id" \
        "from metadata_annotation a join metadata_data s on" \
            "s.participantbarcode = a.itembarcode",
{'fraction_non_alphanumeric': 0.07111830338131335, 'fraction_numerical': 0.035772080742696534, 'mean_word_length': 3.088440111420613, 'pattern_counts': {'":': 60, '<': 4, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '658332', 'n_tokens_mistral': 3403, 'n_tokens_neox': 3059, 'n_words': 970}