def test_export_table(self):
header = ['column1', 'column2']
rows = [['row1_v1', 'row1_v2'], ['row2_v1', 'row2_v2']]
# test tsv format
response = export_table('test_file', header, rows, file_format='tsv')
self.assertEqual(response.status_code, 200)
self.assertEqual(
response.content,
'\n'.join(['\t'.join(row) for row in [header] + rows]) + '\n')
# test Excel format
response = export_table('test_file', header, rows, file_format='xls')
self.assertEqual(response.status_code, 200)
wb = load_workbook(StringIO(response.content))
worksheet = wb.active
self.assertListEqual([cell.value for cell in worksheet['A']],
['Column1', 'row1_v1', 'row2_v1'])
self.assertListEqual([cell.value for cell in worksheet['B']],
['Column2', 'row1_v2', 'row2_v2'])
self.assertEqual([cell.value for cell in worksheet['C']],
[None, None, None])
# test unknown format
self.assertRaisesRegexp(
ValueError, '.*format.*', lambda: export_table(
'test_file', header, rows, file_format='unknown_format'))
def discovery_sheet(request, project_guid=None):
projects = Project.objects.filter(
projectcategory__name__iexact='cmg').distinct()
projects_json = [
_get_json_for_project(project,
request.user,
add_project_category_guids_field=False)
for project in projects
]
projects_json.sort(key=lambda project: project["name"])
rows = []
errors = []
# export table for all cmg projects
if "download" in request.GET and project_guid is None:
logger.info("exporting xls table for all projects")
for project in projects:
if any([
proj_id.lower() == exclude_id.lower() for exclude_id in
PROJECT_IDS_TO_EXCLUDE_FROM_DISCOVERY_SHEET_DOWNLOAD for
proj_id in [project.guid, project.deprecated_project_id]
]):
continue
rows.extend(generate_rows(project, errors))
return export_table("discovery_sheet", HEADER, rows, file_format="xls")
# generate table for 1 project
try:
project = Project.objects.get(guid=project_guid)
except ObjectDoesNotExist:
return render(request, "staff/discovery_sheet.html", {
'projects': projects_json,
'rows': rows,
'errors': errors,
})
rows = generate_rows(project, errors)
logger.info("request.get: " + str(request.GET))
if "download" in request.GET:
logger.info("exporting xls table")
return export_table("discovery_sheet", HEADER, rows, file_format="xls")
return render(
request, "staff/discovery_sheet.html", {
'project': project,
'projects': projects_json,
'header': HEADER.values(),
'rows': rows,
'errors': errors,
})
def komp_export(request):
if "download" in request.GET:
logger.info("exporting komp tags")
start_date = parse(request.GET.get('start_date')).strftime('%Y-%m-%d')
komp_tag_type = VariantTagType.objects.get(guid='VTT_share_with_komp')
variants = VariantTag.objects.filter(variant_tag_type=komp_tag_type,
created_date__gt=start_date)
rows = [{
'project':
v.saved_variant.project.name,
'family_id':
v.saved_variant.family.family_id,
'timestamp':
v.created_date.strftime('%Y-%m-%d %H:%M:%S'),
'genes':
', '.join(
json.loads(v.saved_variant.saved_variant_json)['extras']
['gene_names'].values()),
'chrom':
get_chrom_pos(v.saved_variant.xpos)[0],
'pos':
get_chrom_pos(v.saved_variant.xpos)[1],
'ref':
v.saved_variant.ref,
'alt':
v.saved_variant.alt,
} for v in variants]
return export_table('komp_tags', HEADERS, rows, 'xls')
return render(request, "staff/komp_export.html")
def export_families(filename_prefix, families, file_format, include_project_column=False, include_case_review_columns=False):
"""Export Families table.
Args:
filename_prefix (string): Filename wihtout
families (list): List of Django Family objects to include in the table
file_format (string): "xls" or "tsv"
include_project_column (bool): whether to add a column with the project name
include_case_review_columns (bool): whether to include Case Review-related columns
Returns:
Django HttpResponse object with the table data as an attachment.
"""
header = []
if include_project_column:
header.extend(['project'])
header.extend([
'family_id',
'display_name',
'created_date',
'description',
'analysis_status',
'analysis_summary',
'analysis_notes',
])
if include_case_review_columns:
header.extend([
'internal_case_review_summary',
'internal_case_review_notes',
])
rows = []
analysis_status_lookup = dict(Family.ANALYSIS_STATUS_CHOICES)
for f in families:
row = []
if include_project_column:
row.extend([f.project.name or f.project.project_id])
row.extend([
f.family_id,
f.display_name,
f.created_date,
f.description,
analysis_status_lookup.get(f.analysis_status, f.analysis_status),
_convert_html_to_plain_text(f.analysis_summary, remove_line_breaks=(file_format == 'tsv')),
_convert_html_to_plain_text(f.analysis_notes, remove_line_breaks=(file_format == 'tsv')),
])
if include_case_review_columns:
row.extend([
_convert_html_to_plain_text(f.internal_case_review_summary, remove_line_breaks=(file_format == 'tsv')),
_convert_html_to_plain_text(f.internal_case_review_notes, remove_line_breaks=(file_format == 'tsv')),
])
rows.append(row)
return export_table(filename_prefix, header, rows, file_format)
def export_project_table(request):
file_format = request.GET.get('file_format', 'tsv')
cursor = connection.cursor()
if request.user.is_staff:
projects_user_can_view = Project.objects.all()
else:
projects_user_can_view = Project.objects.filter(can_view_group__user=request.user)
projects_by_guid = _retrieve_projects_by_guid(cursor, projects_user_can_view, [])
#_add_analysis_status_counts(cursor, projects_by_guid)
sample_batches_by_guid = _retrieve_sample_batches_by_guid(cursor, projects_by_guid)
project_categories_by_guid = _retrieve_project_categories_by_guid(projects_by_guid)
cursor.close()
header = [
'project',
'categories',
'num_families',
'num_individuals',
'num_variant_tags',
'created_date',
'num_WES_samples',
'num_WGS_samples',
'num_RNA_samples',
'description',
]
rows = []
for proj in projects_by_guid.values():
project_categories = ', '.join(
[project_categories_by_guid[category_guid]['name'] for category_guid in proj.get('projectCategoryGuids')]
)
num_samples_by_sequecing_type = {}
for sample_batch_guid in proj.get('sampleBatchGuids', []):
sample_batch = sample_batches_by_guid[sample_batch_guid]
num_samples_by_sequecing_type[sample_batch['sequencingType']] = sample_batch['numSamples']
row = [
proj.get('name') or proj.get('deprecatedProjectId'),
project_categories,
proj.get('numFamilies'),
proj.get('numIndividuals'),
proj.get('numVariantTags'),
proj.get('createdDate'),
num_samples_by_sequecing_type.get(SampleBatch.SEQUENCING_TYPE_WES, 0),
num_samples_by_sequecing_type.get(SampleBatch.SEQUENCING_TYPE_WGS, 0),
num_samples_by_sequecing_type.get(SampleBatch.SEQUENCING_TYPE_RNA, 0),
proj.get('description'),
]
rows.append(row)
return export_table('projects', header, rows, file_format)
def export_projects_table_handler(request):
file_format = request.GET.get('file_format', 'tsv')
cursor = connection.cursor()
projects_user_can_view = get_projects_user_can_view(request.user)
projects_by_guid = _retrieve_projects_by_guid(cursor, projects_user_can_view, [])
_add_analysis_status_counts(cursor, projects_by_guid)
_add_sample_type_counts(cursor, projects_by_guid)
project_categories_by_guid = _retrieve_project_categories_by_guid(projects_by_guid)
cursor.close()
header = [
'Project',
'Description',
'Categories',
'Created Date',
'Families',
'Individuals',
'Tagged Variants',
'WES Samples',
'WGS Samples',
'RNA Samples',
]
header.extend([label for key, label in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows = []
for project in sorted(projects_by_guid.values(), key=lambda project: project.get('name') or project.get('deprecatedProjectId')):
project_categories = ', '.join(
[project_categories_by_guid[category_guid]['name'] for category_guid in project.get('projectCategoryGuids')]
)
row = [
project.get('name') or project.get('deprecatedProjectId'),
project.get('description'),
project_categories,
project.get('createdDate'),
project.get('numFamilies'),
project.get('numIndividuals'),
project.get('numVariantTags'),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_WES, 0),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_WGS, 0),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_RNA, 0),
]
row.extend([project.get('analysisStatusCounts', {}).get(key, 0) for key, _ in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows.append(row)
return export_table('projects', header, rows, file_format)
def export_projects_table_handler(request):
file_format = request.GET.get('file_format', 'tsv')
cursor = connection.cursor()
projects_user_can_view = get_projects_user_can_view(request.user)
projects_by_guid = _retrieve_projects_by_guid(cursor, projects_user_can_view, [])
_add_analysis_status_counts(cursor, projects_by_guid)
_add_sample_type_counts(cursor, projects_by_guid)
project_categories_by_guid = _retrieve_project_categories_by_guid(projects_by_guid)
cursor.close()
header = [
'Project',
'Description',
'Categories',
'Created Date',
'Families',
'Individuals',
'Tagged Variants',
'WES Samples',
'WGS Samples',
'RNA Samples',
]
header.extend([label for key, label in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows = []
for project in sorted(projects_by_guid.values(), key=lambda project: project.get('name') or project.get('deprecatedProjectId')):
project_categories = ', '.join(
[project_categories_by_guid[category_guid]['name'] for category_guid in project.get('projectCategoryGuids')]
)
row = [
project.get('name') or project.get('deprecatedProjectId'),
project.get('description'),
project_categories,
project.get('createdDate'),
project.get('numFamilies'),
project.get('numIndividuals'),
project.get('numVariantTags'),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_WES, 0),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_WGS, 0),
project.get('sampleTypeCounts', {}).get(Sample.SAMPLE_TYPE_RNA, 0),
]
row.extend([project.get('analysisStatusCounts', {}).get(key, 0) for key, _ in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows.append(row)
return export_table('projects', header, rows, file_format)
def test_export_table(self):
header = ['column1', 'column2']
rows = [['row1_v1', 'row1_v2'], ['row2_v1', 'row2_v2']]
# test tsv format
response = export_table('test_file', header, rows, file_format='tsv')
self.assertEqual(response.status_code, 200)
self.assertEqual(response.content, '\n'.join(['\t'.join(row) for row in [header]+rows]) + '\n')
# test Excel format
response = export_table('test_file', header, rows, file_format='xls')
self.assertEqual(response.status_code, 200)
wb = load_workbook(StringIO(response.content))
worksheet = wb.active
self.assertListEqual([cell.value for cell in worksheet['A']], ['Column1', 'row1_v1', 'row2_v1'])
self.assertListEqual([cell.value for cell in worksheet['B']], ['Column2', 'row1_v2', 'row2_v2'])
self.assertEqual([cell.value for cell in worksheet['C']], [None, None, None])
# test unknown format
self.assertRaisesRegexp(ValueError, '.*format.*',
lambda: export_table('test_file', header, rows, file_format='unknown_format')
)
def export_variants_handler(request, search_hash):
results_model = VariantSearchResults.objects.get(search_hash=search_hash)
_check_results_permission(results_model, request.user)
family_ids_by_guid = {family.guid: family.family_id for family in results_model.families.all()}
variants, _ = get_es_variants(results_model, page=1, load_all=True)
saved_variants_by_guid = _get_saved_variants(variants)
saved_variants_by_family = defaultdict(dict)
for var in saved_variants_by_guid.values():
for family_guid in var['familyGuids']:
saved_variants_by_family[family_guid]['{}-{}-{}'.format(var['xpos'], var['ref'], var['alt'])] = var
max_families_per_variant = max([len(variant['familyGuids']) for variant in variants])
max_samples_per_variant = max([len(variant['genotypes']) for variant in variants])
rows = []
for variant in variants:
row = [_get_field_value(variant, config) for config in VARIANT_EXPORT_DATA]
for i in range(max_families_per_variant):
family_guid = variant['familyGuids'][i] if i < len(variant['familyGuids']) else ''
family_tags = saved_variants_by_family[family_guid].get('{}-{}-{}'.format(variant['xpos'], variant['ref'], variant['alt'])) or {}
family_tags['family_id'] = family_ids_by_guid.get(family_guid)
row += [_get_field_value(family_tags, config) for config in VARIANT_FAMILY_EXPORT_DATA]
genotypes = variant['genotypes'].values()
for i in range(max_samples_per_variant):
genotype = genotypes[i] if i < len(genotypes) else {}
row += [_get_field_value(genotype, config) for config in VARIANT_GENOTYPE_EXPORT_DATA]
rows.append(row)
header = [config['header'] for config in VARIANT_EXPORT_DATA]
for i in range(max_families_per_variant):
header += ['{}_{}'.format(config['header'], i+1) for config in VARIANT_FAMILY_EXPORT_DATA]
for i in range(max_samples_per_variant):
header += ['{}_{}'.format(config['header'], i+1) for config in VARIANT_GENOTYPE_EXPORT_DATA]
file_format = request.GET.get('file_format', 'tsv')
return export_table('search_results_{}'.format(search_hash), header, rows, file_format, titlecase_header=False)
def export_individuals(
filename_prefix,
individuals,
file_format,
include_project_name=False,
include_project_created_date=False,
include_display_name=False,
include_created_date=False,
include_case_review_status=False,
include_case_review_last_modified_date=False,
include_case_review_last_modified_by=False,
include_case_review_discussion=False,
include_analysis_status=False,
include_coded_phenotype=False,
include_hpo_terms_present=False,
include_hpo_terms_absent=False,
include_paternal_ancestry=False,
include_maternal_ancestry=False,
include_age_of_onset=False,
include_first_loaded_date=False,
):
"""Export Individuals table.
Args:
filename_prefix (string): Filename without the file extension.
individuals (list): List of Django Individual objects to include in the table
file_format (string): "xls" or "tsv"
Returns:
Django HttpResponse object with the table data as an attachment.
"""
header = []
if include_project_name:
header.append('Project')
if include_project_created_date:
header.append('Project Created Date')
header.extend([
'Family ID',
'Individual ID',
'Paternal ID',
'Maternal ID',
'Sex',
'Affected Status',
'Notes',
])
if include_display_name:
header.append('Display Name')
if include_created_date:
header.append('Created Date')
if include_case_review_status:
header.append('Case Review Status')
if include_case_review_last_modified_date:
header.append('Case Review Status Last Modified')
if include_case_review_last_modified_by:
header.append('Case Review Status Last Modified By')
if include_case_review_discussion:
header.append('Case Review Discussion')
if include_analysis_status:
header.append('Analysis Status')
if include_coded_phenotype:
header.append('Coded Phenotype')
if include_hpo_terms_present:
header.append('HPO Terms (present)')
if include_hpo_terms_absent:
header.append('HPO Terms (absent)')
if include_paternal_ancestry:
header.append('Paternal Ancestry')
if include_maternal_ancestry:
header.append('Maternal Ancestry')
if include_age_of_onset:
header.append('Age of Onset')
if include_project_created_date:
header.append('First Data Loaded Date')
rows = []
for i in individuals:
row = []
if include_project_name:
row.extend([i.family.project.name or i.family.project.project_id])
if include_project_created_date:
row.append(i.family.project.created_date)
row.extend([
i.family.family_id,
i.individual_id,
i.father.individual_id if i.father else None,
i.mother.individual_id if i.mother else None,
_SEX_TO_EXPORTED_VALUE.get(i.sex),
__AFFECTED_TO_EXPORTED_VALUE.get(i.affected),
i.notes, # TODO should strip markdown (or be moved to client-side export)
])
if include_display_name:
row.append(i.display_name)
if include_created_date:
row.append(i.created_date)
if include_case_review_status:
row.append(Individual.CASE_REVIEW_STATUS_LOOKUP.get(i.case_review_status, ''))
if include_case_review_last_modified_date:
row.append(i.case_review_status_last_modified_date)
if include_case_review_last_modified_by:
row.append(_user_to_string(i.case_review_status_last_modified_by))
if include_case_review_discussion:
row.append(i.case_review_discussion)
if include_analysis_status:
row.append(i.family.analysis_status)
if include_coded_phenotype:
row.append(i.family.coded_phenotype)
if (include_hpo_terms_present or \
include_hpo_terms_absent or \
include_paternal_ancestry or \
include_maternal_ancestry or \
include_age_of_onset):
if i.phenotips_data:
phenotips_json = json.loads(i.phenotips_data)
phenotips_fields = _parse_phenotips_data(phenotips_json)
else:
phenotips_fields = {}
if include_hpo_terms_present:
row.append(phenotips_fields.get('phenotips_features_present', ''))
if include_hpo_terms_absent:
row.append(phenotips_fields.get('phenotips_features_absent', ''))
if include_paternal_ancestry:
row.append(phenotips_fields.get('paternal_ancestry', ''))
if include_maternal_ancestry:
row.append(phenotips_fields.get('maternal_ancestry', ''))
if include_age_of_onset:
row.append(phenotips_fields.get('age_of_onset', ''))
if include_first_loaded_date:
first_loaded_sample = i.sample_set.filter(
dataset_type=Sample.DATASET_TYPE_VARIANT_CALLS,
loaded_date__isnull=False,
).order_by('loaded_date').first()
row.append(first_loaded_sample.loaded_date if first_loaded_sample else None)
rows.append(row)
return export_table(filename_prefix, header, rows, file_format)
def export_projects_table(request):
file_format = request.GET.get('file_format', 'tsv')
cursor = connection.cursor()
is_staff = request.user.is_staff
if is_staff:
projects_user_can_view = None
else:
projects_user_can_view = Project.objects.filter(can_view_group__user=request.user)
projects_by_guid = _retrieve_projects_by_guid(cursor, projects_user_can_view, [], user_is_staff=is_staff)
_add_analysis_status_counts(cursor, projects_by_guid, user_is_staff=is_staff)
sample_batches_by_guid = _retrieve_sample_batches_by_guid(cursor, projects_by_guid, user_is_staff=is_staff)
project_categories_by_guid = _retrieve_project_categories_by_guid(projects_by_guid, user_is_staff=is_staff)
cursor.close()
header = [
'Project',
'Description',
'Categories',
'Created Date',
'Families',
'Individuals',
'Tagged Variants',
'WES Samples',
'WGS Samples',
'RNA Samples',
]
header.extend([label for key, label in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows = []
for _, proj in sorted(projects_by_guid.items(), key=lambda item: item[1].get('name') or item[1].get('deprecatedProjectId')):
project_categories = ', '.join(
[project_categories_by_guid[category_guid]['name'] for category_guid in proj.get('projectCategoryGuids')]
)
num_samples_by_sequecing_type = {}
for sample_batch_guid in proj.get('sampleBatchGuids', []):
sample_batch = sample_batches_by_guid[sample_batch_guid]
num_samples_by_sequecing_type[sample_batch['sampleType']] = sample_batch['numSamples']
row = [
proj.get('name') or proj.get('deprecatedProjectId'),
proj.get('description'),
project_categories,
proj.get('createdDate'),
proj.get('numFamilies'),
proj.get('numIndividuals'),
proj.get('numVariantTags'),
num_samples_by_sequecing_type.get(SampleBatch.SAMPLE_TYPE_WES, 0),
num_samples_by_sequecing_type.get(SampleBatch.SAMPLE_TYPE_WGS, 0),
num_samples_by_sequecing_type.get(SampleBatch.SAMPLE_TYPE_RNA, 0),
]
row.extend([proj.get('analysisStatusCounts', {}).get(key, 0) for key, _ in Family.ANALYSIS_STATUS_CHOICES if key != 'S'])
rows.append(row)
return export_table('projects', header, rows, file_format)
def export_variants_handler(request, search_hash):
results_model = VariantSearchResults.objects.get(search_hash=search_hash)
_check_results_permission(results_model, request.user)
families = results_model.families.all()
family_ids_by_guid = {family.guid: family.family_id for family in families}
variants, _ = get_es_variants(results_model, page=1, load_all=True)
saved_variants_by_guid, variants_to_saved_variants = _get_saved_variants(
variants, families)
max_families_per_variant = max(
[len(variant['familyGuids']) for variant in variants])
max_samples_per_variant = max(
[len(variant['genotypes']) for variant in variants])
rows = []
for variant in variants:
row = [
_get_field_value(variant, config) for config in VARIANT_EXPORT_DATA
]
for i in range(max_families_per_variant):
family_guid = variant['familyGuids'][i] if i < len(
variant['familyGuids']) else ''
family_tags = saved_variants_by_guid.get(
variants_to_saved_variants.get(variant['variantId'], {}).get(
family_guid, '')) or {}
family_tags['family_id'] = family_ids_by_guid.get(family_guid)
row += [
_get_field_value(family_tags, config)
for config in VARIANT_FAMILY_EXPORT_DATA
]
genotypes = variant['genotypes'].values()
for i in range(max_samples_per_variant):
if i < len(genotypes):
row.append(
'{sampleId}:{numAlt}:{gq}:{ab}'.format(**genotypes[i]))
else:
row.append('')
rows.append(row)
header = [config['header'] for config in VARIANT_EXPORT_DATA]
for i in range(max_families_per_variant):
header += [
'{}_{}'.format(config['header'], i + 1)
for config in VARIANT_FAMILY_EXPORT_DATA
]
header += [
'sample_{}:num_alt_alleles:gq:ab'.format(i + 1)
for i in range(max_samples_per_variant)
]
file_format = request.GET.get('file_format', 'tsv')
return export_table('search_results_{}'.format(search_hash),
header,
rows,
file_format,
titlecase_header=False)
def export_families(
filename_prefix,
families,
file_format,
include_project_name=False,
include_internal_case_review_summary=False,
include_internal_case_review_notes=False,
):
"""Export Families table.
Args:
filename_prefix (string): Filename wihtout
families (list): List of Django Family objects to include in the table
file_format (string): "xls" or "tsv"
Returns:
Django HttpResponse object with the table data as an attachment.
"""
header = []
if include_project_name:
header.append('Project')
header.extend([
'Family ID',
'Display Name',
'Created Date',
'Description',
'Analysis Status',
'Analysis Summary',
'Analysis Notes',
])
if include_internal_case_review_summary:
header.append('Internal Case Review Summary')
if include_internal_case_review_notes:
header.append('Internal Case Review Notes')
rows = []
analysis_status_lookup = dict(Family.ANALYSIS_STATUS_CHOICES)
for family in families:
row = []
if include_project_name:
row.append(family.project.name or family.project.project_id)
row.extend([
family.family_id,
family.display_name,
family.created_date,
family.description,
analysis_status_lookup.get(family.analysis_status,
family.analysis_status),
_convert_html_to_plain_text(
family.analysis_summary,
remove_line_breaks=(file_format == 'tsv')),
_convert_html_to_plain_text(
family.analysis_notes,
remove_line_breaks=(file_format == 'tsv')),
])
if include_internal_case_review_summary:
row.append(
_convert_html_to_plain_text(
family.internal_case_review_summary,
remove_line_breaks=(file_format == 'tsv')), )
if include_internal_case_review_notes:
row.append(
_convert_html_to_plain_text(
family.internal_case_review_notes,
remove_line_breaks=(file_format == 'tsv')), )
rows.append(row)
return export_table(filename_prefix, header, rows, file_format)
def export_individuals(filename_prefix,
individuals,
file_format,
include_project_column=False,
include_display_name=False,
include_dates=False,
include_case_review_columns=False,
include_phenotips_columns=False):
"""Export Individuals table.
Args:
filename_prefix (string): Filename without the file extension.
individuals (list): List of Django Individual objects to include in the table
file_format (string): "xls" or "tsv"
include_project_column (bool):
include_display_name (bool):
include_dates (bool):
include_case_review_columns (bool):
include_phenotips_columns (bool):
Returns:
Django HttpResponse object with the table data as an attachment.
"""
header = []
if include_project_column:
header.extend(['project'])
header.extend([
'family_id',
'individual_id',
'paternal_id',
'maternal_id',
'sex',
'affected_status',
'notes',
])
if include_display_name:
header.extend(['display_name'])
if include_dates:
header.extend(['created_date'])
if include_case_review_columns:
header.extend([
'case_review_status',
'case_review_status_last_modified_date',
'case_review_status_last_modified_by',
'case_review_discussion',
])
if include_phenotips_columns:
phenotips_columns_header = [
'phenotips_features_present', 'phenotips_features_absent',
'paternal_ancestry', 'maternal_ancestry', 'age_of_onset'
]
header.extend(phenotips_columns_header)
rows = []
for i in individuals:
row = []
if include_project_column:
row.extend([i.family.project.name or i.family.project.project_id])
row.extend([
i.family.family_id,
i.individual_id,
i.paternal_id,
i.maternal_id,
_SEX_TO_EXPORT_VALUE.get(i.sex),
_AFFECTED_TO_EXPORT_VALUE.get(i.affected),
_convert_html_to_plain_text(i.notes),
])
if include_display_name:
row.extend([i.display_name])
if include_dates:
row.extend([i.created_date])
if include_case_review_columns:
row.extend([
Individual.CASE_REVIEW_STATUS_LOOKUP.get(
i.case_review_status, ''),
i.case_review_status_last_modified_date,
_user_to_string(i.case_review_status_last_modified_by),
i.case_review_discussion,
])
if include_phenotips_columns:
if i.phenotips_data:
phenotips_json = json.loads(i.phenotips_data)
phenotips_fields = _parse_phenotips_data(phenotips_json)
row.extend(
[phenotips_fields[h] for h in phenotips_columns_header])
else:
row.extend(['' for h in phenotips_columns_header])
rows.append(row)
return export_table(filename_prefix, header, rows, file_format)
def discovery_sheet(request, project_guid=None):
projects = Project.objects.filter(
projectcategory__name__iexact='cmg').prefetch_related(
Prefetch('family_set',
to_attr='families',
queryset=Family.objects.prefetch_related(
'individual_set'))).distinct()
projects_json = [
_get_json_for_project(project,
request.user,
add_project_category_guids_field=False)
for project in projects
]
projects_json.sort(key=lambda project: project["name"])
rows = []
errors = []
# export table for all cmg projects
if "download" in request.GET and project_guid is None:
logger.info("exporting xls table for all projects")
loaded_samples_by_project_family = get_loaded_samples_by_project_family(
projects)
saved_variants_by_project_family = get_saved_variants_by_project_family(
projects)
for project in projects:
rows.extend(
generate_rows(
project,
loaded_samples_by_project_family,
saved_variants_by_project_family,
errors,
update_omim_and_gene_symbols=False,
))
_update_gene_symbols(rows)
_update_initial_omim_numbers(rows)
temp_file = tempfile.NamedTemporaryFile()
wb_out = xl.Workbook()
ws_out = wb_out.active
ws_out.append(map(_to_title_case, HEADER))
for row in rows:
ws_out.append([row[column_key] for column_key in HEADER])
wb_out.save(temp_file.name)
temp_file.seek(0)
email_message = EmailMultiAlternatives(
subject="Discovery Sheet",
body="Attached is the discovery sheet for all seqr projects",
to=[request.user.email],
attachments=[
("discovery_sheet.xlsx", temp_file.read(), "application/xls"),
],
)
email_message.send()
logger.info("emailing discovery sheet to {}".format(
request.user.email))
return create_json_response({'errors': errors})
# generate table for 1 project
project = next(
(project for project in projects if project.guid == project_guid),
None)
if not project:
return render(request, "staff/discovery_sheet.html", {
'projects': projects_json,
'rows': rows,
'errors': errors,
})
loaded_samples_by_project_family = get_loaded_samples_by_project_family(
[project])
saved_variants_by_project_family = get_saved_variants_by_project_family(
[project])
rows = generate_rows(project, loaded_samples_by_project_family,
saved_variants_by_project_family, errors)
logger.info("request.get: " + str(request.GET))
if "download" in request.GET:
logger.info("exporting xls table")
return export_table("discovery_sheet", HEADER, rows, file_format="xls")
return render(
request, "staff/discovery_sheet.html", {
'project': project,
'projects': projects_json,
'header': HEADER.values(),
'rows': rows,
'errors': errors,
})
def export_individuals(
filename_prefix,
individuals,
file_format,
include_project_name=False,
include_display_name=False,
include_created_date=False,
include_case_review_status=False,
include_case_review_last_modified_date=False,
include_case_review_last_modified_by=False,
include_case_review_discussion=False,
include_hpo_terms_present=False,
include_hpo_terms_absent=False,
include_paternal_ancestry=False,
include_maternal_ancestry=False,
include_age_of_onset=False,
):
"""Export Individuals table.
Args:
filename_prefix (string): Filename without the file extension.
individuals (list): List of Django Individual objects to include in the table
file_format (string): "xls" or "tsv"
Returns:
Django HttpResponse object with the table data as an attachment.
"""
header = []
if include_project_name:
header.append('Project')
header.extend([
'Family ID',
'Individual ID',
'Paternal ID',
'Maternal ID',
'Sex',
'Affected Status',
'Notes',
])
if include_display_name:
header.append('Display Name')
if include_created_date:
header.append('Created Date')
if include_case_review_status:
header.append('Case Review Status')
if include_case_review_last_modified_date:
header.append('Case Review Status Last Modified')
if include_case_review_last_modified_by:
header.append('Case Review Status Last Modified By')
if include_case_review_discussion:
header.append('Case Review Discussion')
if include_hpo_terms_present:
header.append('HPO Terms (present)')
if include_hpo_terms_absent:
header.append('HPO Terms (absent)')
if include_paternal_ancestry:
header.append('Paternal Ancestry')
if include_maternal_ancestry:
header.append('Maternal Ancestry')
if include_age_of_onset:
header.append('Age of Onset')
rows = []
for i in individuals:
row = []
if include_project_name:
row.extend([i.family.project.name or i.family.project.project_id])
row.extend([
i.family.family_id,
i.individual_id,
i.paternal_id,
i.maternal_id,
_SEX_TO_EXPORTED_VALUE.get(i.sex),
__AFFECTED_TO_EXPORTED_VALUE.get(i.affected),
_convert_html_to_plain_text(i.notes),
])
if include_display_name:
row.append(i.display_name)
if include_created_date:
row.append(i.created_date)
if include_case_review_status:
row.append(
Individual.CASE_REVIEW_STATUS_LOOKUP.get(
i.case_review_status, ''))
if include_case_review_last_modified_date:
row.append(i.case_review_status_last_modified_date)
if include_case_review_last_modified_by:
row.append(_user_to_string(i.case_review_status_last_modified_by))
if include_case_review_discussion:
row.append(i.case_review_discussion)
if (include_hpo_terms_present or \
include_hpo_terms_absent or \
include_paternal_ancestry or \
include_maternal_ancestry or \
include_age_of_onset):
if i.phenotips_data:
phenotips_json = json.loads(i.phenotips_data)
phenotips_fields = _parse_phenotips_data(phenotips_json)
else:
phenotips_fields = {}
if include_hpo_terms_present:
row.append(
phenotips_fields.get('phenotips_features_present', ''))
if include_hpo_terms_absent:
row.append(
phenotips_fields.get('phenotips_features_absent', ''))
if include_paternal_ancestry:
row.append(phenotips_fields.get('paternal_ancestry', ''))
if include_maternal_ancestry:
row.append(phenotips_fields.get('maternal_ancestry', ''))
if include_age_of_onset:
row.append(phenotips_fields.get('age_of_onset', ''))
rows.append(row)
return export_table(filename_prefix, header, rows, file_format)
