Merge branch 'devel' of github.com:htgt/LIMS2-WebApp into copy_plate

Conflicts: lib/LIMS2/WebApp/Controller/User/PlateCopy.pm
htgt · Mar 13, 2013 · 96622b1 · 96622b1
2 parents e8f54ef + f87159b
commit 96622b1
Show file tree

Hide file tree

Showing 18 changed files with 425 additions and 31 deletions.
diff --git a/Changes b/Changes
@@ -1,5 +1,13 @@
 {{$NEXT}}
 
+0.056     2013-03-11 16:08:02 Europe/London
+
+	Releasing Copy Plate, Genotyping and Final Pick functionality
+
+0.055     2013-03-04 12:02:22 Europe/London
+
+    Amended GenotypingQc upload and automated the appending of the dre recombination to cre wells based on Puro pass.
+
 0.054     2013-02-28 10:45:42 Europe/London
 
 	Added DesignVectors report

diff --git a/ddl/templates/versions/25/fixtures.sql b/ddl/templates/versions/25/fixtures.sql
@@ -0,0 +1,5 @@
+INSERT INTO schema_versions(version)
+VALUES (25);
+
+INSERT INTO process_types ( id, description )
+VALUES( 'legacy_gateway', 'Legacy gateway process from HTGT' );
diff --git a/ddl/templates/versions/25/up.sql b/ddl/templates/versions/25/up.sql
@@ -0,0 +1 @@
+ALTER TABLE designs ALTER COLUMN phase DROP NOT NULL;
diff --git a/lib/LIMS2/Model/Constants.pm b/lib/LIMS2/Model/Constants.pm
@@ -26,6 +26,7 @@ const our %PROCESS_PLATE_TYPES => (
     cre_bac_recom          => [qw( INT )],
     '2w_gateway'           => [qw( POSTINT FINAL )],
     '3w_gateway'           => [qw( POSTINT FINAL )],
+    legacy_gateway         => [qw( FINAL_PICK )],
     final_pick             => [qw( FINAL_PICK )],
     dna_prep               => [qw( DNA )],
     recombinase            => [qw( FINAL XEP POSTINT )],
@@ -76,6 +77,10 @@ const our %PROCESS_INPUT_WELL_CHECK => (
         type   => [qw( INT )],
         number => 1,
     },
+    legacy_gateway => {
+        type   => [qw( INT )],
+        number => 1,
+    },
     final_pick => {
         type   => [qw( FINAL FINAL_PICK )],
         number => 1,

diff --git a/lib/LIMS2/Model/Plugin/Design.pm b/lib/LIMS2/Model/Plugin/Design.pm
@@ -45,7 +45,7 @@ sub pspec_create_design {
         type                    => { validate => 'existing_design_type', rename => 'design_type_id' },
         created_at              => { validate => 'date_time', post_filter => 'parse_date_time', optional => 1 },
         created_by              => { validate => 'existing_user', post_filter => 'user_id_for' },
-        phase                   => { validate => 'phase' },
+        phase                   => { validate => 'phase', optional => 1 },
         validated_by_annotation => { validate => 'validated_by_annotation', default => 'not done' },
         name                    => { validate => 'alphanumeric_string', optional => 1 },
         target_transcript       => { optional => 1, validate => 'ensembl_transcript_id' },

diff --git a/lib/LIMS2/Model/Plugin/GenotypingQC.pm b/lib/LIMS2/Model/Plugin/GenotypingQC.pm
@@ -399,6 +399,21 @@ my $saved_id = -1;
 my $datum;
 my $gene_cache;
 
+# Extract the well_ids from $sql_result and send them to create_well_cache to generate
+# a cache of well objects as a hashref. Squeeze out the duplicates along the way.
+my @well_id_list;
+my %seen;
+foreach my $row ( @{$sql_result} ) {
+    my $well_id = $row->{'Well ID'};
+    if ( !$seen{$well_id} ) {
+        push @well_id_list, $well_id;
+        $seen{$well_id} = 1;
+    }
+}
+
+my $design_well_cache = $self->create_design_well_cache( \@well_id_list );
+
+
 $self->log->debug ('SQL query brought back ' . @{$sql_result} . ' rows.' );
 foreach my $row ( @{$sql_result} ) {
     if ( $row->{'Well ID'} != $saved_id ) {
@@ -423,20 +438,24 @@ foreach my $row ( @{$sql_result} ) {
             $datum->{$row->{'Primer band type'}} = ($row->{'Primer pass?'} ? 'true' : 'false') // '-' ;
         }
 
-        my $well = $self->retrieve_well( { id => $datum->{id} } );
+        # simply lookup the source well id in the design_well_cache
-        my ($design) = $well->designs;
+        my $design_well = $design_well_cache->{$datum->{'id'}}->{'design_well_ref'};
+        my $design = $design_well_cache->{$datum->{'id'}}->{'design_ref'};
         $datum->{gene_id} = '-';
         $datum->{gene_id} = $design->genes->first->gene_id if $design;
+        $datum->{gene_name} = '-';
         # If we have already seen this gene_id don't go searching for it again
         if ( $gene_cache->{$datum->{gene_id} } ) {
             $datum->{gene_name} = $gene_cache->{ $datum->{gene_id} };
         }
         else {
-            $datum->{gene_name} = $self->get_gene_symbol_for_accession( $well, $species);
+            if ( $design_well ) {
-            $gene_cache->{$datum->{gene_id}} = $datum->{gene_name};
+                $datum->{gene_name} = $self->get_gene_symbol_for_accession( $design_well, $species);
+                $gene_cache->{$datum->{gene_id}} = $datum->{gene_name};
+            }
         }
         $datum->{design_id} = '-';
-        $datum->{design_id} = $design->id if $design;
+        $datum->{design_id} = $design_well->id if $design_well;
         # get the generic assay data for this row
         if ( $row->{'genotyping_result_type_id'}) {
             $datum->{$row->{'genotyping_result_type_id'} . '#' . 'call'} =  $row->{'call'} // '-';
@@ -478,14 +497,14 @@ sub pspec_get_gene_symbol_for_accession {
 # This will return a '/' separated list of symbols for a given accession and species.
 # TODO: send in a list of accessions and return a list of gene symbols thus mimimising the overhead of calls to search_genes.
 sub get_gene_symbol_for_accession{
-    my ($self, $well, $species, $params) = @_;
+    my ($self, $design_well, $species, $params) = @_;
 
     my $genes;
     my $gene_symbols;
     my @gene_symbols;
 
-    my ($design) = $well->designs;
+    my ($design) = $design_well->designs;
-    if ( $design) {
+    if ( $design ) {
         my @gene_ids = uniq map { $_->gene_id } $design->genes;
         foreach my $gene_id ( @gene_ids ) {
             $genes = $self->search_genes(
@@ -497,4 +516,52 @@ sub get_gene_symbol_for_accession{
 
     return $gene_symbols;
 }
+
+# We need a design well cache so that we can call methods on the design well itself
+#
+sub create_design_well_cache {
+    my $self = shift;
+    my $well_id_list_ref = shift;
+
+    # Use a ProcessTree method to get the list of design wells.
+    my $design_well_hash = $self->get_design_wells_for_well_id_list( $well_id_list_ref );
+
+    # create a list of wells to fetch
+    # also keep a record of design_id => source_well_id for later use
+    my @design_well_id_list;
+    my %seen;
+    foreach my $well_id ( keys %{$design_well_hash} ) {
+        my $design_well_id = $design_well_hash->{$well_id}->{'design_well_id'};
+        if ( !$seen{$design_well_id} ) {
+            push @design_well_id_list, $design_well_id;
+            $seen{$design_well_id} = 1;
+        }
+    }
+    my $design_well_rs = $self->schema->resultset( 'Well' );
+    my @design_wells = $design_well_rs->search(
+        {
+            'me.id' => { '-in' => \@design_well_id_list }
+        },
+    );
+    # Get the designs for each design well and cache them ...
+    my $designs_hash_ref;
+
+    foreach my $design_well ( @design_wells ) {
+        ($designs_hash_ref->{$design_well->id}) = $design_well->designs;
+    }
+
+    # save a reference to the design well in the appropriate key/value
+    foreach my $well_id ( keys %{$design_well_hash} ) {
+        # match up the design_well_id
+        my $design_well_id = $design_well_hash->{$well_id}->{'design_well_id'};
+        MATCH_DESIGN_WELL: foreach my $design_well ( @design_wells ) {
+            if ( $design_well->id == $design_well_id ) {
+               $design_well_hash->{$well_id}->{'design_well_ref'} = $design_well;
+               $design_well_hash->{$well_id}->{'design_ref'} = $designs_hash_ref->{$design_well->id};
+               last MATCH_DESIGN_WELL;
+            }
+        }
+    }
+    return $design_well_hash;
+}
 1;
diff --git a/lib/LIMS2/Model/Plugin/Plate.pm b/lib/LIMS2/Model/Plugin/Plate.pm
@@ -325,7 +325,7 @@ sub plate_help_info {
     my %plate_info;
 
     for my $process ( keys %PROCESS_PLATE_TYPES ) {
-        next if $process eq 'create_di';
+        next if $process =~ /create_di|legacy_gateway/;
         $plate_info{$process}{plate_types} = $PROCESS_PLATE_TYPES{$process};
         $plate_info{$process}{data}
             = exists $PROCESS_SPECIFIC_FIELDS{$process} ? $PROCESS_SPECIFIC_FIELDS{$process} : [];

diff --git a/lib/LIMS2/Model/Plugin/ProcessTree.pm b/lib/LIMS2/Model/Plugin/ProcessTree.pm
@@ -93,7 +93,7 @@ WHERE w.process_id = pd.process_id
 GROUP BY w.process_id, w.input_well_id, w.output_well_id, pd.design_id,"original_well", w.path;
 QUERY_END
 
-my $QUERY_ANCESTORS_BY_WELL_ID = << 'QUERY_END';
+my $QUERY_ANCESTORS_BY_WELL_ID_WITH_PATHS = << 'QUERY_END';
 WITH RECURSIVE well_hierarchy(process_id, input_well_id, output_well_id, path) AS (
 -- Non-recursive term
      SELECT pr.id, pr_in.well_id, pr_out.well_id, ARRAY[pr_out.well_id] 
@@ -116,13 +116,41 @@ WHERE w.process_id = pd.process_id
 GROUP BY w.process_id, w.input_well_id, w.output_well_id, pd.design_id,"original_well", w.path;
 QUERY_END
 
+my $QUERY_ANCESTORS_BY_WELL_ID = << 'QUERY_END';
+WITH RECURSIVE well_hierarchy(process_id, input_well_id, output_well_id, path) AS (
+-- Non-recursive term
+     SELECT pr.id, pr_in.well_id, pr_out.well_id, ARRAY[pr_out.well_id] 
+     FROM processes pr
+     LEFT OUTER JOIN process_input_well pr_in ON pr_in.process_id = pr.id
+     JOIN process_output_well pr_out ON pr_out.process_id = pr.id
+        WHERE pr_out.well_id = ?
+     UNION ALL
+-- Recursive term
+     SELECT pr.id, pr_in.well_id, pr_out.well_id, path || pr_out.well_id
+     FROM processes pr
+     LEFT OUTER JOIN process_input_well pr_in ON pr_in.process_id = pr.id
+     JOIN process_output_well pr_out ON pr_out.process_id = pr.id
+     JOIN well_hierarchy ON well_hierarchy.input_well_id = pr_out.well_id
+)
+SELECT w.output_well_id, pd.design_id, w.path[1] "original_well"
+FROM well_hierarchy w, process_design pd
+WHERE w.process_id = pd.process_id
+--ORDER BY pd.design_id;
+GROUP BY w.output_well_id, pd.design_id, "original_well";
+QUERY_END
+
 sub pspec_paths_for_well_id_depth_first {
     return {
         well_id           => { validate   => 'integer', },
         direction         => { validate   => 'boolean', optional => 1 },
     };
 }
 
+=heading1 get_paths_for_well_id_depth_first
+
+returns paths as well_ids
+=cut
+
 sub get_paths_for_well_id_depth_first {
     my $self = shift;
     my ($params) = @_;
@@ -153,14 +181,124 @@ sub get_paths_for_well_id_depth_first {
     return \@paths;
 }
 
-sub pspec_designs_for_plate {
+=heading1 get_well_paths_for_well_id
+
+Returns well objects.
+=cut
+
+#TODO:
+=head
+    my $rs = $self->schema->resultset( 'Well' )->search(
+        {
+            'me.id' => { '-in' => \@well_ids }
+        },
+        {
+            prefetch => [ 'plate' ]
+        }
+    );
+=cut
+
+sub pspec_design_wells_for_well_id {
     return {
-        plate_name  =>{ validate => 'string', },
+        well_id           => { validate   => 'integer', },
-        direction => { validate => 'boolean', optional=> 1 },
     };
 }
 
-sub get_designs_for_plate {
+=heading1 get_design_wells_for_well_id
+
+=cut
+
+sub get_design_wells_for_well_id {
+    my $self = shift;
+    my ($params) = @_;
+
+    my $validated_params = $self->check_params( $params, $self->pspec_design_wells_for_well_id);
+
+    my $sql_query = $QUERY_ANCESTORS_BY_WELL_ID;
+    my $sql_result =  $self->schema->storage->dbh_do(
+    sub {
+         my ( $storage, $dbh ) = @_;
+         my $sth = $dbh->prepare_cached( $sql_query );
+         $sth->execute( $validated_params->{'well_id'} );
+         $sth->fetchall_arrayref();
+        }
+    );
 
+    my $result_hash;
+
+    foreach my $result ( @{$sql_result} ) {
+        $result_hash->{$result->[2]} = {
+            'design_well_id' => $result->[0],
+            'design_id'      => $result->[1],
+        }
+    }
+
+    return $result_hash;
+}
+
+
+sub pspec_design_wells_for_well_id_list {
+    return {
+         'wells'  ,
+    };
+}
+
+sub get_design_wells_for_well_id_list {
+    my $self = shift;
+    my $wells = shift;
+#    my ($params) = @_;
+
+#    my $validated_params = $self->check_params( $params, $self->pspec_design_wells_for_well_id_list);
+
+    my $well_list = join q{,}, @{$wells};
+
+my $QUERY_ANCESTORS_BY_WELL_LIST = << "QUERY_END";
+WITH RECURSIVE well_hierarchy(process_id, input_well_id, output_well_id, path) AS (
+-- Non-recursive term
+     SELECT pr.id, pr_in.well_id, pr_out.well_id, ARRAY[pr_out.well_id] 
+     FROM processes pr
+     LEFT OUTER JOIN process_input_well pr_in ON pr_in.process_id = pr.id
+     JOIN process_output_well pr_out ON pr_out.process_id = pr.id
+     WHERE pr_out.well_id IN (
+        $well_list
+     )	
+     UNION ALL
+-- Recursive term
+     SELECT pr.id, pr_in.well_id, pr_out.well_id, path || pr_out.well_id
+     FROM processes pr
+     LEFT OUTER JOIN process_input_well pr_in ON pr_in.process_id = pr.id
+     JOIN process_output_well pr_out ON pr_out.process_id = pr.id
+     JOIN well_hierarchy ON well_hierarchy.input_well_id = pr_out.well_id
+)
+SELECT w.output_well_id, pd.design_id, w.path[1] "original_well"
+FROM well_hierarchy w, process_design pd
+WHERE w.process_id = pd.process_id
+--ORDER BY pd.design_id;
+GROUP BY w.output_well_id, pd.design_id,"original_well";
+QUERY_END
+
+    my $sql_query = $QUERY_ANCESTORS_BY_WELL_LIST;
+    my $sql_result =  $self->schema->storage->dbh_do(
+    sub {
+         my ( $storage, $dbh ) = @_;
+         my $sth = $dbh->prepare_cached( $sql_query );
+         $sth->execute();
+         $sth->fetchall_arrayref();
+        }
+    );
+
+    my $result_hash;
+
+    foreach my $result ( @{$sql_result} ) {
+        $result_hash->{$result->[2]} = {
+            'design_well_id' => $result->[0],
+            'design_id'      => $result->[1],
+        }
+    }
+    # The format of the resulting hash is:
+    # well_id => {
+    #   design_well_id => integer_id,
+    #   design_id => integer_id }
+    return $result_hash;
 }
 1;