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)