-- -----------------------------------------------------------------------------
--	SQL statements to export a summary of gene models data, for checking.
--
--	James Allen - 31st August 2006
-- -----------------------------------------------------------------------------

CREATE TEMPORARY TABLE tmp_fid_entrez_gene
	SELECT DISTINCT t1.fid, t2.fattribute_value AS gene_id
	FROM fdata t1
	INNER JOIN fattribute_to_feature t2 ON t1.fid=t2.fid
	INNER JOIN fattribute t3 ON t2.fattribute_id=t3.fattribute_id
	WHERE t3.fattribute_name = 'EntrezGene';
CREATE INDEX tmp_fid_entrez_gene_idx ON tmp_fid_entrez_gene(fid);
ALTER TABLE tmp_fid_entrez_gene MODIFY gene_id INTEGER(10);
CREATE INDEX tmp_fid_entrez_gene_idx2 ON tmp_fid_entrez_gene(gene_id);

CREATE TEMPORARY TABLE tmp_fid_position_variant
	SELECT DISTINCT t1.fid, t2.fattribute_value AS position_variant
	FROM fdata t1
	INNER JOIN fattribute_to_feature t2 ON t1.fid=t2.fid
	INNER JOIN fattribute t3 ON t2.fattribute_id=t3.fattribute_id
	WHERE t3.fattribute_name = 'PositionVariant';
CREATE INDEX tmp_fid_position_variant_idx ON tmp_fid_position_variant(fid);

SELECT DISTINCT t1.fref, t1.fstrand, FLOOR(MIN(t1.fstart)/1000000),
	t2.gene_id, t3.fsource, t4.gname, t5.position_variant
FROM fdata t1
INNER JOIN tmp_fid_entrez_gene t2 ON t1.fid=t2.fid
INNER JOIN ftype t3 ON t1.ftypeid=t3.ftypeid
INNER JOIN fgroup t4 ON t1.gid=t4.gid
INNER JOIN fattribute_to_feature t7 ON t1.fid=t7.fid
LEFT OUTER JOIN tmp_fid_position_variant t5 ON t1.fid=t5.fid
LEFT OUTER JOIN xy_gene t6 ON t2.gene_id=t6.entrezgene_id
WHERE t1.fref NOT LIKE '%hap%'
	AND t1.fref NOT LIKE '%random'
	AND (t7.fattribute_value = "NCBI37.1" OR t7.fattribute_value = "NCBIM37" OR t7.fattribute_value = "RGSC3.4")
	AND t6.entrezgene_id IS NULL
GROUP BY t1.fref, t1.fstrand, t2.gene_id, t3.fsource, t4.gname, t5.position_variant
ORDER BY t2.gene_id, t3.fsource, t1.fref, t1.fstrand, t5.position_variant;

