cellularLocalization.dst

[templateStart]
name=geneImageGoTermQuestion
anchorFile=ApiCommonModel/Model/lib/wdk/model/questions/geneQuestions.xml
prop=datasetName
prop=datasetDisplayName
prop=datasetShortDisplayName
prop=buildNumberIntroduced
prop=includeProjects
>templateTextStart<

    <question name="GenesByGoTermCL_${datasetName}"
         displayName="${datasetDisplayName} Cellular Localization Imaging"
         shortDisplayName="${datasetShortDisplayName} GO CL"
         queryRef="GeneId.GenesByGoTermCLDataset"
         recordClassRef="TranscriptRecordClasses.TranscriptRecordClass"
         includeProjects="${includeProjects}"
         newBuild="${buildNumberIntroduced}">

        <paramRef ref="geneParams.cellularLocImageDataset" default="${datasetName}" visible="false"/>
        <paramRef ref="geneParams.go_typeahead_cl"/>
        <paramRef ref="geneParams.go_term"/>


        <summary>
            <![CDATA[
           Find genes based on GO cellular component terms and view cellular localization images from ${datasetDisplayName}.
            ]]>
        </summary>

        <description>
            <![CDATA[
             Search for genes by GO cellular component terms and visualize their cellular localization using images from ${datasetDisplayName}.
            ]]>
        </description>

    </question>

>templateTextEnd<


[templateStart]
name=geneImageGoTermOntology
anchorFile=ApiCommonModel/Model/lib/wdk/ontology/individuals.txt
prop=datasetName
>templateTextStart<
TranscriptRecordClasses.TranscriptRecordClass.GeneQuestions.GenesByGoTermCL_${datasetName}	searchCategory-cellular-localization-goassoc	Protein targeting and localization	TranscriptRecordClasses.TranscriptRecordClass	search	GeneQuestions.GenesByGoTermCL_${datasetName}							webservice
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaQuestion
anchorFile=ApiCommonModel/Model/lib/wdk/model/questions/geneQuestions.xml
prop=presenterId
prop=datasetName
prop=datasetDisplayName
prop=datasetShortDisplayName
prop=buildNumberIntroduced
prop=includeProjects
prop=cellularLocalizationWdkAttributes
>templateTextStart<
    <question name="GenesByCellularLocalizationEdaSubset_${datasetName}" includeProjects="${includeProjects}" newBuild="${buildNumberIntroduced}"
         displayName="${datasetDisplayName} Cellular Localization"
         shortDisplayName="${datasetShortDisplayName} (QCL)"
         queryRef="GeneId.GenesByEdaSubsetGeneric"
         recordClassRef="TranscriptRecordClasses.TranscriptRecordClass">

        <paramRef ref="geneParams.eda_dataset_id" default="${presenterId}" visible="false"/>
        <paramRef ref="geneParams.eda_analysis_spec" allowEmpty="true" prompt="Filter genes based on cellular localization data"/>

<!-- TODO - add in attributesList, when available: ${cellularLocalizationWdkAttributes} -->
         <attributesList
              summary="organism,gene_location_text,gene_product"
        />

	<summary><![CDATA[
        Filter genes by Cellular Localization Data.
          ]]>
        </summary>
    <description> <![CDATA[
        Filter genes by Cellular Localization Data.  Click on a filter on the left to view data and apply a filter.  You can apply as many filters as you choose.
          ]]>
        </description>
    </question>
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaGeneTableSql
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/geneTableQueries.xml
prop=datasetName
prop=edaStudyStableId
prop=edaEntityAbbrev
>templateTextStart<
               UNION
               SELECT '${datasetName}' AS dataset_name , string_value AS source_id
               FROM eda.ATTRIBUTEvalue_${edaStudyStableId}_${edaEntityAbbrev} av
               WHERE av.attribute_stable_id = 'VEUPATHDB_GENE_ID'
>templateTextEnd<


[templateStart]
name=cellularLocalizationDataTableGeneTableSql
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/geneTableQueries.xml
prop=datasetName
prop=edaStudyStableId
prop=edaEntityAbbrev
>templateTextStart<
UNION
SELECT genes.string_value AS gene,
       ag.display_name AS variable,
       av.string_value,
       av.number_value,
       av.date_value,
       '${datasetName}' AS dataset_id
FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av,
     eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev} ag,
     (SELECT av.${edaEntityAbbrev}_stable_id, MIN(gi.gene) as string_value
      FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av
      JOIN apidbtuning.GeneId gi ON gi.id = av.string_value
      WHERE av.attribute_stable_id = 'VEUPATHDB_GENE_ID'
      GROUP BY av.${edaEntityAbbrev}_stable_id) genes
WHERE av.attribute_stable_id = ag.stable_id
  AND av.${edaEntityAbbrev}_stable_id = genes.${edaEntityAbbrev}_stable_id
  AND av.attribute_stable_id != 'VEUPATHDB_GENE_ID'
>templateTextEnd<


[templateStart]
name=cellularLocalizationAllDataGeneTableSql
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/geneTableQueries.xml
prop=datasetName
prop=edaStudyStableId
prop=edaEntityAbbrev
prop=datasetDisplayName
prop=shortAttribution
>templateTextStart<
UNION
SELECT genes.string_value AS gene,
       '${datasetName}' AS dataset_presenter_id,
       '${datasetDisplayName}' AS dataset_presenter_display_name,
       '${shortAttribution}' AS short_attribution,
       ag.display_name AS variable,
       coalesce(av.string_value, coalesce(round(av.number_value::numeric, 4)::text, av.date_value::text)) AS value
FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av,
     eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev} ag,
     (SELECT av.${edaEntityAbbrev}_stable_id, MIN(gi.gene) as string_value
      FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av
      JOIN apidbtuning.GeneId gi ON gi.id = av.string_value
      WHERE av.attribute_stable_id = 'VEUPATHDB_GENE_ID'
      GROUP BY av.${edaEntityAbbrev}_stable_id) genes
WHERE av.attribute_stable_id = ag.stable_id
  AND av.${edaEntityAbbrev}_stable_id = genes.${edaEntityAbbrev}_stable_id
  AND av.attribute_stable_id != 'VEUPATHDB_GENE_ID'
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaAttributeQueriesNumeric
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/transcriptAttributeQueries.xml
prop=datasetName
prop=edaStudyStableId
prop=edaEntityAbbrev
prop=includeProjects
>templateTextStart<
        <sqlQuery name="CellularLocalizationVariablesNumeric${datasetName}"
                  doNotTest="true"
                  attributeMetaQueryRef="TranscriptAttributes.MetaCellularLocalizationVariablesNumeric${datasetName}"
                  includeProjects="${includeProjects}">
            <column name="source_id"/>
            <column name="gene_source_id"/>
            <column name="project_id"/>
            <sql>
              <![CDATA[
  SELECT ta.source_id, ta.project_id, ta.gene_source_id, p.*
  FROM apidbtuning.TranscriptAttributes ta
    LEFT OUTER JOIN (
      SELECT * FROM public.crosstab(
        'SELECT genes.string_value as gene_id
             ,  CONCAT(''${edaStudyStableId}_'', av.attribute_stable_id) as attribute_stable_id
             , av.number_value
         FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av,
              eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev} ag,
              (SELECT av.${edaEntityAbbrev}_stable_id, MIN(gi.gene) as string_value
               FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av
               JOIN apidbtuning.GeneId gi ON gi.id = av.string_value
               WHERE av.attribute_stable_id = ''VEUPATHDB_GENE_ID''
               GROUP BY av.${edaEntityAbbrev}_stable_id) genes
         WHERE av.${edaEntityAbbrev}_stable_id = genes.${edaEntityAbbrev}_stable_id
           AND av.attribute_stable_id != ''VEUPATHDB_GENE_ID''
	   AND av.attribute_stable_id = ag.stable_id
	   AND ag.has_values = 1
           AND (ag.data_type = ''number'' OR ag.data_type = ''integer'')
	   AND (ag.hidden IS NULL OR NOT (ag.hidden::jsonb @> ''["variableTree"]''::jsonb))
         ORDER BY 1, 2',
        'SELECT CONCAT(''${edaStudyStableId}_'', stable_id) as stable_id
         FROM eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev}
         WHERE has_values = 1
           AND stable_id != ''VEUPATHDB_GENE_ID''
           AND (data_type = ''number'' OR data_type = ''integer'')
           AND (hidden IS NULL OR NOT (hidden::jsonb @> ''["variableTree"]''::jsonb))
         ORDER BY stable_id'
      ) AS ct(gene_id text, &&META_ATTRIBUTE_COLUMNS_FOR_CROSSTAB&&)
    ) p ON ta.gene_source_id = p.gene_id
              ]]>
            </sql>
        </sqlQuery>

       <sqlQuery name="MetaCellularLocalizationVariablesNumeric${datasetName}" includeProjects="${includeProjects}">
          <column name="name" />
          <column name="display_name" />
          <column name="help" />
          <column name="reporter_name" />
          <column name="reporter_display" />
          <column name="reporter_description" />
          <column name="reporter_implementation" />
          <column name="reporter_properties" />

          <sql>
            <![CDATA[
           SELECT CONCAT('${edaStudyStableId}_', stable_id) as name,
                  display_name,
                  'number' as column_type,
                  'Cellular Localization variable from ${datasetName}' as help,
                  'histogram' as reporter_name,
                  'Histogram' as reporter_display,
                  'Display a histogram of the values' as reporter_description,
                  'org.gusdb.wdk.model.report.reporter.HistogramAttributeReporter' as reporter_implementation,
                  null as reporter_properties
           FROM eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev}
           WHERE has_values = 1
             AND (data_type = 'number' OR data_type = 'integer')
             AND stable_id != 'VEUPATHDB_GENE_ID'
             AND (hidden IS NULL OR NOT (hidden::jsonb @> '["variableTree"]'::jsonb))
           ORDER BY stable_id
            ]]>
          </sql>
       </sqlQuery>
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaAttributeQueriesString
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/transcriptAttributeQueries.xml
prop=datasetName
prop=edaStudyStableId
prop=edaEntityAbbrev
prop=includeProjects
>templateTextStart<
        <sqlQuery name="CellularLocalizationVariablesString${datasetName}"
                  doNotTest="true"
                  attributeMetaQueryRef="TranscriptAttributes.MetaCellularLocalizationVariablesString${datasetName}"
                  includeProjects="${includeProjects}">
            <column name="source_id"/>
            <column name="gene_source_id"/>
            <column name="project_id"/>
            <sql>
              <![CDATA[
  SELECT ta.source_id, ta.project_id, ta.gene_source_id, p.*
  FROM apidbtuning.TranscriptAttributes ta
    LEFT OUTER JOIN (
      SELECT * FROM public.crosstab(
        'SELECT genes.string_value as gene_id
             ,  CONCAT(''${edaStudyStableId}_'', av.attribute_stable_id) as attribute_stable_id
             , av.string_value
         FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av,
              eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev} ag,
              (SELECT av.${edaEntityAbbrev}_stable_id, MIN(gi.gene) as string_value
               FROM eda.attributevalue_${edaStudyStableId}_${edaEntityAbbrev} av
               JOIN apidbtuning.GeneId gi ON gi.id = av.string_value
               WHERE av.attribute_stable_id = ''VEUPATHDB_GENE_ID''
               GROUP BY av.${edaEntityAbbrev}_stable_id) genes
         WHERE av.${edaEntityAbbrev}_stable_id = genes.${edaEntityAbbrev}_stable_id
           AND av.attribute_stable_id != ''VEUPATHDB_GENE_ID''
	   AND av.attribute_stable_id = ag.stable_id
	   AND ag.has_values = 1
           AND ag.data_type NOT IN (''number'', ''integer'')
	   AND (ag.hidden IS NULL OR NOT (ag.hidden::jsonb @> ''["variableTree"]''::jsonb))
         ORDER BY 1, 2',
        'SELECT CONCAT(''${edaStudyStableId}_'', stable_id) as stable_id
         FROM eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev}
         WHERE has_values = 1
           AND stable_id != ''VEUPATHDB_GENE_ID''
           AND data_type NOT IN (''number'', ''integer'')
           AND (hidden IS NULL OR NOT (hidden::jsonb @> ''["variableTree"]''::jsonb))
         ORDER BY stable_id'
      ) AS ct(gene_id text, &&META_ATTRIBUTE_COLUMNS_FOR_CROSSTAB&&)
    ) p ON ta.gene_source_id = p.gene_id
              ]]>
            </sql>
        </sqlQuery>

       <sqlQuery name="MetaCellularLocalizationVariablesString${datasetName}" includeProjects="${includeProjects}">
          <column name="name" />
          <column name="display_name" />
          <column name="help" />
          <column name="reporter_name" />
          <column name="reporter_display" />
          <column name="reporter_description" />
          <column name="reporter_implementation" />
          <column name="reporter_properties" />

          <sql>
            <![CDATA[
           SELECT CONCAT('${edaStudyStableId}_', stable_id) as name,
                  display_name,
                  'string' as column_type,
                  'Cellular Localization variable from ${datasetName}' as help,
                  'wordCloud' as reporter_name,
                  'Word Cloud' as reporter_display,
                  'Display a word cloud of the values' as reporter_description,
                  'org.eupathdb.common.model.report.EbrcWordCloudAttributeReporter' as reporter_implementation,
                  null as reporter_properties
           FROM eda.attributegraph_${edaStudyStableId}_${edaEntityAbbrev}
           WHERE has_values = 1
             AND data_type NOT IN ('number', 'integer')
             AND stable_id != 'VEUPATHDB_GENE_ID'
             AND (hidden IS NULL OR NOT (hidden::jsonb @> '["variableTree"]'::jsonb))
           ORDER BY stable_id
            ]]>
          </sql>
       </sqlQuery>
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaAttributeRef
anchorFile=ApiCommonModel/Model/lib/wdk/model/records/transcriptRecord.xml
prop=datasetName
prop=includeProjects
>templateTextStart<
              <attributeQueryRef ref="TranscriptAttributes.CellularLocalizationVariablesNumeric${datasetName}" attributeMetaQueryRef="TranscriptAttributes.MetaCellularLocalizationVariablesNumeric${datasetName}" includeProjects="${includeProjects}">
              </attributeQueryRef>
              <attributeQueryRef ref="TranscriptAttributes.CellularLocalizationVariablesString${datasetName}" attributeMetaQueryRef="TranscriptAttributes.MetaCellularLocalizationVariablesString${datasetName}" includeProjects="${includeProjects}">
              </attributeQueryRef>
>templateTextEnd<


[templateStart]
name=cellularLocalizationEdaAttributeCategory
anchorFile=ApiCommonModel/Model/lib/wdk/ontology/individuals.txt
prop=datasetName
prop=datasetDisplayName
>templateTextStart<
CellularLocalizationDataset_${datasetName}	http://edamontology.org/topic_0140	Protein targeting and localization	DatasetRecordClasses.DatasetRecordClass	dataset	${datasetName}	${datasetDisplayName}	results
TranscriptAttributes.MetaCellularLocalizationVariablesNumeric${datasetName}	CellularLocalizationDataset_${datasetName}		TranscriptRecordClasses.TranscriptRecordClass	attributeMetaQuery	MetaCellularLocalizationVariablesNumeric${datasetName}				gene		results	download
TranscriptAttributes.MetaCellularLocalizationVariablesString${datasetName}	CellularLocalizationDataset_${datasetName}		TranscriptRecordClasses.TranscriptRecordClass	attributeMetaQuery	MetaCellularLocalizationVariablesString${datasetName}				gene		results	download
>templateTextEnd<