package org.ensembl.healthcheck.testcase.compara;

import org.ensembl.healthcheck.DatabaseRegistryEntry;
import org.ensembl.healthcheck.DatabaseType;
import org.ensembl.healthcheck.ReportManager;
import org.ensembl.healthcheck.Team;
import org.ensembl.healthcheck.testcase.AbstractTemplatedTestCase;
import org.ensembl.healthcheck.util.DBUtils;

public class MemberProductionValues extends AbstractTemplatedTestCase {

    public MemberProductionValues() {
        setTeamResponsible(Team.COMPARA);
        appliesToType(DatabaseType.COMPARA);
        setDescription("Checks whether the gene_member_hom_stats table has been properly populated");
    }

    @Override
    protected boolean runTest(DatabaseRegistryEntry dbre) {
        boolean result = true;

        result = computeMissingValues (dbre);

        return result;
    }

    protected boolean computeMissingValues(DatabaseRegistryEntry dbre) {
        boolean result = true;

        //Get the total counts
        String sql_1 = "SELECT gene_member_id FROM gene_member_hom_stats WHERE collection = 'murinae'";
        int totalCount = DBUtils.getRowCount(dbre.getConnection(), sql_1);

        //Get the missing counts
        String sql_2 = "SELECT gene_member_id FROM gene_member_hom_stats WHERE gene_trees = 1 AND gene_gain_loss_trees = 0 AND orthologues = 0 AND paralogues = 0 AND homoeologues = 0";
        int missingCount = DBUtils.getRowCount(dbre.getConnection(), sql_2);

        // Will fail if more than 0.1% of values are missing
        if(missingCount > (totalCount*0.001)) {
            ReportManager.problem(this, dbre.getConnection(), "Too many missing counts (>0.1%) in gene_member_hom_stats. Wrong homology counts.");
            result = false;
        }
        return result;
    }
}

@@ -44,16 +44,39 @@
 mpara;%0A%0A
+import java.util.Map;%0A%0A
 import o
@@ -360,16 +360,73 @@
 DBUtils;
+%0Aimport org.ensembl.healthcheck.util.DefaultMapRowMapper;
 %0A%0Apublic
@@ -978,32 +978,33 @@
 try dbre) %7B%0A
+%0A
         
 boolean resu
@@ -995,61 +995,104 @@
     
-boolean result = true;%0A%0A        //Get the total count
+// For each collection, get the percentage of genes that are in a tree but without any homologue
 s%0A  
@@ -1107,18 +1107,16 @@
 ring sql
-_1
  = %22SELE
@@ -1114,38 +1114,143 @@
 l = %22SELECT 
-gene_member_id
+collection, 100 * SUM(gene_trees %3E 0 AND orthologues = 0 AND paralogues = 0 AND homoeologues = 0) / SUM(gene_trees %3E 0)
  FROM gene_m
@@ -1265,21 +1265,24 @@
 m_stats 
-WHERE
+GROUP BY
  collect
@@ -1288,131 +1288,132 @@
 tion
- = 'murinae'%22;%0A        int totalCount = DBUtils.getRowCount(dbre.getConnection(), sql_1);%0A%0A        //Get the missing counts
+%22;%0A        DefaultMapRowMapper%3CString, Double%3E mapper = new DefaultMapRowMapper%3CString, Double%3E(String.class, Double.class);
 %0A   
@@ -1421,263 +1421,201 @@
     
+Map%3C
 String
- sql_2 = %22SELECT gene_member_id FROM gene_member_hom_stats WHERE gene_trees = 1 AND gene_gain_loss_trees = 0 AND orthologues = 0 AND paralogues = 0 AND homoeologues = 0%22;%0A        int missingCount = DBUtils.getRowCount(dbre.getConnection(), sql_2);%0A%0A
+,Double%3E percentageMissingData = getSqlTemplate(dbre).queryForMap(sql, mapper);%0A%0A        boolean result = true;%0A        for (String collection : percentageMissingData.keySet()) %7B%0A    
     
@@ -1625,14 +1625,9 @@
  // 
-Will f
+F
 ail 
@@ -1678,48 +1678,67 @@
     
-if(missingCount %3E (totalCount*0.001)) %7B%0A
+    if (percentageMissingData.get(collection) %3E 0.1) %7B%0A    
     
@@ -1809,55 +1809,144 @@
 any 
-missing counts (%3E0.1%25) in gene_member_hom_stats
+genes without homologues (%22 + percentageMissingData.get(collection) + %22%25) in gene_member_hom_stats for the collection %22 + collection + %22
 . Wr
@@ -1973,32 +1973,36 @@
 %22);%0A            
+    
 result = false;%0A
@@ -2001,16 +2001,30 @@
  false;%0A
+            %7D%0A
         

check the count for each collection