package ubic.gemma.model.association.phenotype;

import java.math.BigInteger;
import java.util.Collection;
import java.util.HashSet;

import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import ubic.gemma.model.genome.gene.phenotype.valueObject.CharacteristicValueObject;
import ubic.gemma.persistence.AbstractDao;

@Repository
public class PhenotypeAssociationDaoImpl extends AbstractDao<PhenotypeAssociation> implements PhenotypeAssociationDao {

    @Autowired
    public PhenotypeAssociationDaoImpl( SessionFactory sessionFactory ) {
        super( PhenotypeAssociationImpl.class );
        super.setSessionFactory( sessionFactory );
    }

    /** find Genes for specific phenotypes */
    public Collection<Long> findByPhenotype( String... phenotypesValues ) {

        Collection<Long> genesNCBI = new HashSet<Long>();

        int numberOfterms = phenotypesValues.length;

        if ( numberOfterms == 0 ) {
            return null;
        }

        String queryStringPart1 = "SELECT t1.ID FROM CHROMOSOME_FEATURE t1 INNER JOIN (SELECT t3.* FROM PHENOTYPE_ASSOCIATION t3 INNER JOIN (SELECT PHENOTYPE_ASSOCIATION_FK,COUNT(*) FROM CHARACTERISTIC WHERE";

        String queryStringPart2 = "";

        for ( int i = 0; i < numberOfterms; i++ ) {
            queryStringPart2 = queryStringPart2 + " value = '" + phenotypesValues[i] + "' ";

            // if not the last one
            if ( i != numberOfterms - 1 ) {
                queryStringPart2 = queryStringPart2 + "or";
            }
        }

        String queryStringPart3 = "group by PHENOTYPE_ASSOCIATION_FK HAVING COUNT(*) = " + numberOfterms
                + " ) t4 ON t3.id = t4.PHENOTYPE_ASSOCIATION_FK) t2 ON t1.id = t2.GENE_FK";

        String queryString = queryStringPart1 + queryStringPart2 + queryStringPart3;

        org.hibernate.SQLQuery queryObject = this.getSession().createSQLQuery( queryString );
        ScrollableResults results = queryObject.scroll( ScrollMode.FORWARD_ONLY );
        while ( results.next() ) {
            genesNCBI.add( ( ( BigInteger ) results.get( 0 ) ).longValue() );
        }

        return genesNCBI;
    }

    public Collection<CharacteristicValueObject> findAllPhenotypes() {

        Collection<CharacteristicValueObject> phenotypes = new HashSet<CharacteristicValueObject>();

        String queryString = "select value, value_uri, category, category_uri, count(*) from CHARACTERISTIC where phenotype_association_fk is not null group by value";
        org.hibernate.SQLQuery queryObject = this.getSession().createSQLQuery( queryString );

        ScrollableResults results = queryObject.scroll( ScrollMode.FORWARD_ONLY );
        while ( results.next() ) {

            String value = ( String ) results.get( 0 );
            String valueUri = ( String ) results.get( 1 );
            String category = ( String ) results.get( 2 );
            String categoryUri = ( String ) results.get( 3 );
            long count = ( ( BigInteger ) results.get( 4 ) ).longValue();

            CharacteristicValueObject characteristicValueObject = new CharacteristicValueObject( value, category,
                    valueUri, categoryUri );
            characteristicValueObject.setOccurence( count );
            phenotypes.add( characteristicValueObject );
        }
        results.close();

        return phenotypes;
    }
}

@@ -855,16 +855,53 @@
 ypes */%0A
+    // TODO should be and HSQL query%0A
     publ
@@ -1200,13 +1200,14 @@
 ECT 
-t1.ID
+chr.id
  FRO
@@ -1227,18 +1227,185 @@
 FEATURE 
-t1
+chr%22;%0A%0A        String queryStringPart2 = %22%22;%0A%0A        for ( int i = 0; i %3C numberOfterms; i++ ) %7B%0A            queryStringPart2 = queryStringPart2%0A                    + %22
  INNER J
@@ -1413,16 +1413,17 @@
 IN (
+ 
 SELECT t
 3.* 
@@ -1422,122 +1422,375 @@
 CT t
-3.* FROM PHENOTYPE_ASSOCIATION t3 INNER JOIN (SELECT PHENOTYPE_ASSOCIATION_FK,COUNT(*) FROM CHARACTERISTIC WHERE%22;
+%22%0A                    + i%0A                    + %22.ID FROM CHROMOSOME_FEATURE t%22%0A                    + i%0A                    + %22 WHERE id IN (SELECT gene_fk FROM CHARACTERISTIC INNER JOIN PHENOTYPE_ASSOCIATION ON PHENOTYPE_ASSOCIATION.ID = CHARACTERISTIC.PHENOTYPE_ASSOCIATION_FK WHERE VALUE = '%22%0A                    + phenotypesValues%5Bi%5D + %22'))t%22 + i + %22 %22;%0A        %7D
 %0A%0A  
@@ -1809,37 +1809,40 @@
  queryStringPart
-2
+3
  = %22
+ON 
 %22;%0A%0A        for 
@@ -1900,33 +1900,33 @@
  queryStringPart
-2
+3
  = queryStringPa
@@ -1931,51 +1931,35 @@
 Part
-2
+3
  + %22
- value = '
+chr.id = t
 %22 + 
-phenotypesValues%5Bi%5D
+i
  + %22
-'
+.id
  %22;%0A
@@ -2061,33 +2061,33 @@
  queryStringPart
-2
+3
  = queryStringPa
@@ -2092,15 +2092,17 @@
 Part
-2
+3
  + %22
-or
+AND 
 %22;%0A 
@@ -2129,206 +2129,8 @@
  %7D%0A%0A
-        String queryStringPart3 = %22group by PHENOTYPE_ASSOCIATION_FK HAVING COUNT(*) = %22 + numberOfterms%0A                + %22 ) t4 ON t3.id = t4.PHENOTYPE_ASSOCIATION_FK) t2 ON t1.id = t2.GENE_FK%22;%0A%0A
     
@@ -3758,10 +3758,11 @@
 ;%0A    %7D%0A
+%0A
 %7D%0A

Change a service findPhenotypes, we can now find genes containing 1 to many phenotypes ( bag of terms) done by gene (can be on multiple evidences)