#MANUAL updates to table_column


UPDATE table_column
  SET optionlist_query = '
SELECT ARS.array_request_sample_id, convert(varchar(20),ARS.array_request_sample_id) || '' - '' || 
       CASE WHEN L.array_request_sample_id IS NOT NULL THEN ''*DONE* - '' ELSE '''' END ||
       ARS.name || '' {'' || LM.name || ''}'', L.array_request_sample_id
  FROM $TBMA_ARRAY_REQUEST_SAMPLE ARS
  LEFT JOIN $TBMA_LABELING L ON ( ARS.array_request_sample_id = L.array_request_sample_id )
  LEFT JOIN $TBMA_LABELING_METHOD LM ON ( ARS.labeling_method_id = LM.labeling_method_id )
  LEFT JOIN $TBMA_ARRAY_REQUEST_SLIDE ARSL ON ( ARS.array_request_slide_id = ARSL.array_request_slide_id )
  LEFT JOIN $TBMA_ARRAY_REQUEST AR ON ( ARSL.array_request_id = AR.array_request_id )
  WHERE AR.project_id = $project_id
 ORDER BY ARS.array_request_sample_id'
WHERE table_name = 'MA_labeling'
   AND column_name = 'array_request_sample_id'
GO


UPDATE table_column
  SET optionlist_query = '
SELECT ARS.array_request_slide_id, convert(varchar(20),ARS.array_request_slide_id)||
       CASE WHEN A.array_request_slide_id IS NOT NULL THEN '' - *DONE*'' ELSE '''' END
  FROM $TBMA_ARRAY_REQUEST_SLIDE ARS
  LEFT JOIN $TBMA_ARRAY A ON ( ARS.array_request_slide_id=A.array_request_slide_id )
 ORDER BY ARS.array_request_slide_id'
 WHERE table_name = 'MA_array'
   AND column_name = 'array_request_slide_id'
GO


UPDATE table_column
  SET optionlist_query = '
SELECT S.array_scan_id,
       array_name||CASE WHEN Q.array_scan_id IS NOT NULL THEN '' - *DONE*'' ELSE '''' END,
       Q.array_scan_id
  FROM $TBMA_ARRAY A 
  LEFT JOIN $TBMA_ARRAY_SCAN S ON ( A.array_id=S.array_id ) 
  LEFT JOIN $TBMA_ARRAY_QUANTITATION Q ON ( S.array_scan_id = Q.array_scan_id )
 WHERE S.array_id IS NOT NULL
 ORDER BY A.array_name'
 WHERE table_name = 'MA_array_quantitation'
   AND column_name = 'array_scan_id'
GO


UPDATE table_column
  SET optionlist_query = '
SELECT ST.slide_type_id, name||''   ( \$''||CONVERT(varchar(50),STC.price)||'' )''
  FROM $TBMA_SLIDE_TYPE ST
  JOIN $TBMA_SLIDE_TYPE_COST STC ON ( ST.slide_type_id = STC.slide_type_id )
 WHERE STC.cost_scheme_id=$cost_scheme_id
 ORDER BY sort_order,name'
 WHERE table_name = 'MA_array_request'
   AND column_name = 'slide_type_id'
GO


UPDATE table_column
  SET optionlist_query = '
SELECT DISTINCT condition_id,username || '' - '' || project_tag || '' -> '' || condition_name,username,project_tag,condition_name
  FROM $TBMA_COMPARISON_CONDITION C
 INNER JOIN $TB_PROJECT P ON ( C.project_id = P.project_id )
 INNER JOIN $TB_USER_LOGIN UL ON ( P.PI_contact_id = UL.contact_id )
 WHERE P.project_id IN ( $parameters{project_id} )
 ORDER BY username,project_tag,condition_name'
 WHERE (   table_name = 'MA_GetExpression'
       )
   AND column_name = 'condition_id'
GO


UPDATE table_column
  SET optionlist_query = '
SELECT DISTINCT affy_array_id ,username || '' - '' || project_tag || '' -> '' || file_root
  FROM $TBMA_AFFY_ARRAY afa
 INNER JOIN $TBMA_AFFY_ARRAY_SAMPLE afs ON (afa.affy_array_sample_id = afs.affy_array_sample_id)
 INNER JOIN $TB_PROJECT P ON ( afs.project_id = P.project_id )
 INNER JOIN $TB_USER_LOGIN UL ON ( P.PI_contact_id = UL.contact_id )
 WHERE P.project_id IN ( $parameters{project_id} ) AND
 afa.affy_array_id IN (SELECT DISTINCT affy_array_id FROM $TBMA_AFFY_GENE_INTENSITY)
 ORDER BY  username || '' - '' || project_tag || '' -> '' || file_root, affy_array_id'
 WHERE (   table_name = 'MA_GetAffy_GeneIntensity'
       )
   AND column_name = 'affy_array_id'
GO

UPDATE table_column
  SET optionlist_query = '
SELECT DISTINCT P.project_id,UL.username||'' - ''||P.name 
FROM $TB_PROJECT P 
INNER JOIN $TBMA_AFFY_ARRAY_SAMPLE afs ON ( P.project_id = afs.project_id )
INNER JOIN $TBMA_AFFY_ARRAY afa ON (afa.affy_array_sample_id = afs.affy_array_sample_id)  
LEFT JOIN $TB_USER_LOGIN UL ON ( P.PI_contact_id=UL.contact_id ) 
WHERE P.record_status != ''D'' 
AND afa.affy_array_id in (SELECT DISTINCT(affy_array_id) FROM $TBMA_AFFY_GENE_INTENSITY gi)
AND P.project_id IN ($accessible_project_ids)
ORDER BY UL.username||'' - ''||P.name,P.project_id'
 WHERE (   table_name = 'MA_GetAffy_GeneIntensity'
       )
   AND column_name = 'project_id'


GO

UPDATE table_column
  SET optionlist_query = '
SELECT affy_annotation_set_id, annotation_date || '' SLIDE TYPE = '' || st.name || '' GENOME BUILD = '' || anno_set.genome_version 
FROM $TBMA_AFFY_ANNOTATION_SET anno_set 
JOIN $TBMA_SLIDE_TYPE st ON (st.slide_type_id = anno_set.slide_type_id)
ORDER BY annotation_date || '' SLIDE TYPE = '' || st.name || '' GENOME BUILD = '' || anno_set.genome_version '
WHERE (      table_name = 'MA_GetAffy_GeneIntensity'
	  OR table_name = 'MA_affy_array_sample'
	)
    AND column_name = 'annotation_set_constraint'
    
GO




UPDATE table_column
  SET optionlist_query = '
SELECT 
MOT2.MGED_ontology_term_id,
MOT2.name 
FROM $TBBL_MGED_ONTOLOGY_RELATIONSHIP MOR 
INNER JOIN $TBBL_MGED_ONTOLOGY_TERM MOT2 ON ( MOR.subject_term_id = MOT2.MGED_ontology_term_id ) 
WHERE MOR.object_term_id in 
(SELECT MGED_ontology_term_id FROM $TBBL_MGED_ONTOLOGY_TERM WHERE name = ''Sex'') 
ORDER BY MOT2.name'

WHERE (   table_name = 'MA_array_request_sample'
	OR table_name = 'MA_affy_array_sample'
	)
    AND column_name = 'sex_ontology_term_id'