lib/CXGN/DbStats.pm

   1
   2 package CXGN::DbStats;
   3
   4 use Moose;
   5
   6 has 'dbh' => (isa => 'Ref', is => 'rw');
   7
   8 # retrieve all trials grouped by trial type
   9 #
  10 sub trial_types {
  11     my $self = shift;
  12     my $q = "SELECT cvterm.name, count(*) from projectprop join cvterm on(type_id=cvterm_id) JOIN cv USING (cv_id) WHERE cv_id=(SELECT cv_id FROM cv WHERE name='project_type') GROUP BY cvterm.name ORDER BY count(*) desc";
  13     my $h = $self->dbh->prepare($q);
  14     $h->execute();
  15     return $h->fetchall_arrayref();
  16 }
  17
  18 # retrieve all trials grouped by breeding programs
  19 #
  20 sub trials_by_breeding_program {
  21     my $self = shift;
  22     my $q = "select project.name, count(*) from project join project_relationship on (project.project_id=project_relationship.object_project_id) join project as trial on(subject_project_id=trial.project_id) join projectprop on(project.project_id = projectprop.project_id) join cvterm on (projectprop.type_id=cvterm.cvterm_id) join projectprop as trialprop on(trial.project_id = trialprop.project_id) join cvterm as trialcvterm on(trialprop.type_id=trialcvterm.cvterm_id) where cvterm.name='breeding_program' and trialcvterm.name in (SELECT cvterm.name FROM cvterm join cv using(cv_id) WHERE cv.name='project_type') group by project.name order by count(*) desc";
  23     my $h = $self->dbh->prepare($q);
  24     $h->execute();
  25     return $h->fetchall_arrayref();
  26 }
  27
  28 # retrieve all the traits measured with counts
  29 #
  30 sub traits {
  31     my $self = shift;
  32     my $q = "select cvterm.name, count(*) from phenotype join cvterm on (observable_id=cvterm_id)  group by cvterm.name order by count(*) desc";
  33     my $h = $self->dbh->prepare($q);
  34     $h->execute();
  35     return $h->fetchall_arrayref();
  36 }
  37
  38 sub stocks {
  39     my $self = shift;
  40     my $q = "SELECT cvterm.name, count(*) FROM stock join cvterm on(type_id=cvterm_id) GROUP BY cvterm.name ORDER BY count(*) desc";
  41     my $h = $self->dbh->prepare($q);
  42     $h->execute();
  43     return $h->fetchall_arrayref();
  44 }
  45
  46 sub basic {
  47     my $self = shift;
  48     my $q = "select count(*) from ";
  49 }
  50
  51 sub activity {
  52     my $self = shift;
  53
  54     my @counts;
  55     my @weeks;
  56     foreach my $week (0..51) {
  57         my $days = $week * 7;
  58         my $previous_days = ($week + 1) * 7;
  59         my $q = "SELECT count(*) FROM nd_experiment WHERE create_date > (now() - INTERVAL '$previous_days DAYS') and create_date < (now() - INTERVAL '$days DAYS')";
  60         my $h = $self->dbh()->prepare($q);
  61         $h->execute();
  62         my ($count) = $h->fetchrow_array();
  63
  64         print STDERR "Activity in week $week = $count\n";
  65
  66         push @counts, { letter => $week, frequency => $count };
  67         #push @weeks, $week;
  68     }
  69     return \@counts;
  70 }
  71
  72
  73
  74 1;