lib/SGN/Controller/Status.pm

   1
   2 package SGN::Controller::Status;
   3
   4 use Moose;
   5 use Data::Dumper;
   6
   7 BEGIN { extends "Catalyst::Controller"; }
   8
   9
  10
  11 sub status : Path('/status') Args(0) {
  12     my $self = shift;
  13     my $c = shift;
  14
  15     my $sp_person_id = $c->user() ? $c->user->get_object()->get_sp_person_id() : undef;
  16     my $schema = $c->dbic_schema("Bio::Chado::Schema", 'sgn_chado', $sp_person_id);
  17     my $dbh = $schema->storage->dbh();
  18     my $h;
  19
  20     # Get Accession count
  21     $h = $dbh->prepare("SELECT COUNT(accession_id) AS count FROM accessions;");
  22     $h->execute();
  23     my ($accession_count) = $h->fetchrow_array() and $h->finish();
  24
  25     # Get Breeding Program Count
  26     $h = $dbh->prepare("SELECT COUNT(breeding_program_id) AS count FROM breeding_programs;");
  27     $h->execute();
  28     my ($breeding_program_count) = $h->fetchrow_array() and $h->finish();
  29
  30     # Get Count of Lines with Pheno Data
  31     $h = $dbh->prepare("SELECT COUNT(DISTINCT accession_id) AS count FROM accessionsxplots;");
  32     $h->execute();
  33     my ($accession_count_pheno) = $h->fetchrow_array() and $h->finish();
  34
  35     # Get Count of Lines with Geno Data
  36     $h = $dbh->prepare("SELECT COUNT(DISTINCT accession_id) AS count FROM accessionsxgenotyping_protocols WHERE genotyping_protocol_id IS NOT NULL;");
  37     $h->execute();
  38     my ($accession_count_geno) = $h->fetchrow_array() and $h->finish();
  39
  40     # Get Trait Count
  41     $h = $dbh->prepare("SELECT COUNT(DISTINCT observable_id) AS count FROM phenotype;");
  42     $h->execute();
  43     my ($trait_count) = $h->fetchrow_array() and $h->finish();
  44
  45     # Get Pheno Trial Count
  46     $h = $dbh->prepare("SELECT COUNT(trial_id) AS count FROM trials;");
  47     $h->execute();
  48     my ($pheno_trial_count) = $h->fetchrow_array() and $h->finish();
  49
  50     # Get Count of Total Pheno Observations
  51     $h = $dbh->prepare("SELECT COUNT(DISTINCT phenotype_id) FROM materialized_phenoview WHERE phenotype_id IS NOT NULL;");
  52     $h->execute();
  53     my ($pheno_observations) = $h->fetchrow_array() and $h->finish();
  54
  55     # Get last pheno addition date
  56     $h = $dbh->prepare("SELECT MAX(create_date) FROM phenotype;");
  57     $h->execute();
  58     my ($pheno_last_addition) = $h->fetchrow_array() and $h->finish();
  59
  60     # Get genotype protocol count
  61     $h = $dbh->prepare("SELECT COUNT(genotyping_protocol_id) FROM genotyping_protocols;");
  62     $h->execute();
  63     my ($geno_protocol_count) = $h->fetchrow_array() and $h->finish();
  64
  65     # Get marker count
  66     $h = $dbh->prepare("SELECT COUNT(marker_id) FROM sgn.marker;");
  67     $h->execute();
  68     my ($marker_count) = $h->fetchrow_array() and $h->finish();
  69
  70     # Get last geno addition date
  71     $h = $dbh->prepare("SELECT MAX(create_date) FROM genotype;");
  72     $h->execute();
  73     my ($geno_last_addition) = $h->fetchrow_array() and $h->finish();
  74
  75     # Pass query results to template
  76     $c->stash->{accession_count} = $accession_count;
  77     $c->stash->{breeding_program_count} = $breeding_program_count;
  78     $c->stash->{accession_count_pheno} = $accession_count_pheno;
  79     $c->stash->{accession_count_geno} = $accession_count_geno;
  80     $c->stash->{trait_count} = $trait_count;
  81     $c->stash->{pheno_trial_count} = $pheno_trial_count;
  82     $c->stash->{pheno_observations} = $pheno_observations;
  83     $c->stash->{pheno_last_addition} = $pheno_last_addition;
  84     $c->stash->{geno_protocol_count} = $geno_protocol_count;
  85     $c->stash->{marker_count} = $marker_count;
  86     $c->stash->{geno_last_addition} = $geno_last_addition;
  87     $c->stash->{template} = '/about/sgn/status.mas';
  88 }
  89
  90 1;