Audio::DB::Reports - Generate quick reports of a Audio::DB database

  print join("\t",$album,$artist,$year),"\n";

    - listing all albums with associated artists and year released
    - finding albums that fall below a given bitrate threshold
    - finding albums with multiple bitrates

package Audio::DB::Reports;
# $Id: Reports.pm,v 1.2 2005/02/27 16:56:25 todd Exp $

# THIS MODULE SHOULD REALLY ONLY HOLD COLLECTIVE METHODS THAT GENERATE REPORTS

use strict 'vars';
use Carp 'croak','cluck';
use CGI qw/:standard *table *TR -no_xhtml *center/;
use DBI;
use vars qw(@ISA $VERSION);

use Audio::DB;
use Audio::DB::Query;
use Audio::DB::Util::Warnings;
use Audio::DB::Util::DataAccess;
use Audio::DB::Util::Rearrange;

@ISA = qw/
  Audio::DB
  Audio::DB::Query 
  Audio::DB::Util::DataAccess
  Audio::DB::Util::Warnings/;

$VERSION = '';

#############################
#   ALBUM REPORTS
#############################

# The internal data strcuture for album reports looks like this
#
#  $self = [ {
#                 album    => album name,
#                 album_id => album id,
#                 artists  => [ all contributing artists ],
#                 songs    => [ all contributing songs   ],
#                 bitrates => [ bitrates seen for the album ],
#	  	  years    => [ years seen for the album ],
# Also included in the hash ref but not necessarily accurate because of compilation CDs
#                 year     => year released
#                 artist   => primary artist
#               } ]
#


sub album_list {
  my ($self,@p) = @_;
  my ($sort_by,$include) = rearrange([qw/SORT_BY INCLUDE/],@p);
  my $albums = $self->fetch_class(-class=>'albums');
  my %include = map {$_ => 1 } @$include;
  if (defined $include{songs}) {
    foreach my $album (@$albums) {
      my $songs = $self->fetch(-class=>'song',-query=>$album->album_id,-perspective=>'by_album_id');
      $album->add_songs($songs);
    }
  }

  if ($sort_by) {
    my @sorted = _sort_albums($sort_by,$albums);
    return \@sorted;
  }
  return $albums;
}

sub albums_with_multiple_bitrates {
  my ($self,@p) = @_;
  my ($sort_by) = rearrange([qw/SORT_BY/],@p);
  my $adaptor = $self->adaptor;

  my $albums = $self->fetch_class(-class=>'album');
  foreach my $album (@$albums) {
    my $songs = $self->fetch(-class=>'song',-query=>$album->album_id,-perspective=>'by_album_id');
    $album->add_songs($songs);
  }

  my @retain;
  foreach (@$albums) {
    my $bitrate = $_->bitrates;
    next unless $bitrate eq 'various';
    push @retain,$_;
  }

  if ($sort_by) {
    my @sorted = _sort_albums($sort_by,\@retain);
    return \@sorted;
  }
  return \@retain;
}

# ATTEMPTING TO OPTIMIZE...
#sub albums_below_bitrate_threshold {
#  my ($self,@p) = @_;
#  my ($threshold,$sort_by,$include_songs) = rearrange([qw/THRESHOLD SORT_BY INCLUDE_SONGS/],@p);
#  my $adaptor = $self->adaptor;
#  my $sth = $adaptor->prepare('albums_below_bitrate_threshold');
#  $sth->execute($threshold);
#  my @return;
#  while (my $h = $sth->fetchrow_hashref) {
#    # Create new album objects for each
#    my $obj = Audio::DB::DataTypes::Album->new(-adaptor=>$adaptor,-data=>$h);
#    my $id = $obj->album_id;
#    # Need to touch songs to get artists and bitrates (could use seperate queries, too).
#    my $songs = $self->fetch_songs(-album=>$id);
#    @{$obj->{songs}} = @$songs if ($include_songs);  # Stuff in the songs, too, if requested
#    my %seen;
#    push(@{$obj->{artists}},grep { !$seen{$_}++} map { $_->artist  } @$songs);
#    push(@{$obj->{artists}},grep { !$seen{$_}++} map { $_->bitrate } @$songs);
#    push @return,$obj;
#  }
#  return \@return;
#}


sub albums_below_bitrate_threshold {
  my ($self,@p) = @_;
  my ($threshold,$sort_by,$include) = rearrange([qw/THRESHOLD SORT_BY INCLUDE/],@p);
  $include = [qw/songs/] unless @$include;  # We need to add in the songs in order to fetch all bitrates
  my $albums  = $self->fetch_class(-class=>'albums');
  my %include = map {$_ => 1 } @$include;
  if (defined $include{songs}) {
    foreach my $album (@$albums) {
      my $songs = $self->fetch(-class=>'song',-query=>$album->album_id,-perspective=>'by_album_id');
      $album->add_songs($songs);
    }
  }

  my @retain;
  foreach (@$albums) {
    my $bitrate = $_->bitrates;
    unless ($bitrate eq 'various') {
      next if ($bitrate >= $threshold);
    }
    push @retain,$_;
  }

  if ($sort_by) {
    my @sorted = _sort_albums($sort_by,\@retain);
    return \@sorted;
  }
  return \@retain;
}

# Internal album sorting method (specific since I need to call methods that do not correspond to column names)
sub _sort_albums {
  my ($sort_by,$albums) = @_;
  my @sorted;
  if ($sort_by eq 'artist') {
    @sorted = sort { $a->artists cmp $b->artists } @$albums;
  } elsif ($sort_by eq 'year') {
    @sorted  = sort {$a->years <=> $b->years } @$albums;
  } elsif ($sort_by eq 'album') {
    @sorted  = sort {$a->album cmp $b->album } @$albums;
  }
  return @sorted;
}

#############################
#   ARTIST REPORTS
#############################

sub artists_with_multiple_genres {
  my ($self,@p) = @_;
  my $adaptor = $self->adaptor;
  my $artists = $self->fetch(-class=>'artist',-perspective=>'artists_multiple_genres');
  foreach my $artist (@$artists) {
    # Fetch the genres associated with each artist...
    my $genres = $self->fetch(-class=>'genre',-perspective=>'by_artist_id',-query=>$artist->artist_id);
    @{$artist->{genres}} = @$genres;
  }
  return $artists;
}

#############################
#   SONG REPORTS
#############################

sub song_list {
  my ($self,@p) = @_;
  my ($sort_by) = rearrange([qw/SORT_BY/],@p);
  my $songs = $self->fetch_class(-class=>'songs');
  if ($sort_by) {
    my @sorted = _generic_sort($sort_by,$songs);
    return \@sorted;
  }
  return $songs;
}

# I NEED SOME WAY OF DISTINGUISHING THINGS THAT SHOULD BE
# NUMERIC SORT FROM ALPHABETIC SORT
sub _genric_sort {
  my ($sort_by,$data) = @_;
  my @sorted = sort { $a->$sort_by cmp $b->$sort_by } @$data;
  return @sorted;
}


#############################
#   GENRE REPORTS
#############################
# How many songs/albums/artists of each genre?
sub genre_report {
  my ($self,@p) = @_;
  my ($sort_by,$include) = rearrange([qw/SORT_BY INCLUDE/],@p);
  my $sth;
  
  my $genres = $self->fetch(-class=>'genre',-perspective=>'all_genres');
  my @return;
  foreach my $genre (@$genres) {
    # How many songs?
    my $songs   = $self->fetch(-class=>'song',  -perspective=>'by_genre_id',-query=>$genre->genre_id);
    my $albums  = $self->fetch(-class=>'album', -perspective=>'by_genre_id',-query=>$genre->genre_id);
    my $artists = $self->fetch(-class=>'artist',-perspective=>'by_genre_id',-query=>$genre->genre_id);
    push (@{$genre->{stats}},scalar @$songs,scalar @$albums,scalar @$artists);
    if ($include) {
      @{$genre->{songs}}   = @$songs;
      @{$genre->{albums}}  = @$albums;
      @{$genre->{artists}} = @$artists;
    }
    push @return,$genre;
  }
  return wantarray ? @return : \@return;
}



#############################
#   GENERIC STATISTICS
#############################

sub distribution {
  my ($self,@p) = @_;
  my ($class,$width,$height,$range,$background,$foreground,$omit_totals)
    = rearrange([qw/CLASS WIDTH HEIGHT RANGE BACKGROUND FOREGROUND OMIT_TOTALS/],@p);

  eval { use GD; };# or die "You need to install the GD.pm module in order to use the album_distibution() method";

  # Set up some suitable defaults
  use constant DEBUG => 0;
  $width    ||= 600;
  $height   ||= 550;

  # Padding around all sides of the image
  my $PADRIGHT  = 20;
  my $PADLEFT   = 50;
  my $PADTOP    = 10;
  my $PADBOTTOM = 50;

  my $adaptor = $self->adaptor;
  my $sth = ($class eq 'songs') 
    ? $adaptor->song_queries('songs_per_year') : $adaptor->album_queries('albums_per_year');

  # Calculate the totals for each year
  my %years_seen;
  foreach (@$range) {
    $sth->execute($_);
    my ($result) = $sth->fetchrow_array;
    $years_seen{$_} = $result;
  }

  # Find the year which contains the most albums to dynamically set the y-scale
  my @sorted_years = sort { $years_seen{$a} <=> $years_seen{$b} } @$range;
  my $biggest_year = $years_seen{$sorted_years[-1]};
  my $yscale = ($height - ($PADTOP + $PADBOTTOM) - 30) / $biggest_year;

  print STDERR "y-scale: $yscale\n" if DEBUG;
  print STDERR "biggest year: $sorted_years[-1], $biggest_year\n" if DEBUG;

  my $im = new GD::Image($width,$height);

  push(@$background,255,255,0) unless @$background;
  my ($bred,$bgreen,$bblue) = @{$background};
  my $bg = $im->colorAllocate($bred,$bgreen,$bblue);

  push(@$foreground,0,255,255) unless @$foreground;
  my ($fred,$fgreen,$fblue) = @$foreground;
  my $fg    = $im->colorAllocate($fred,$fgreen,$fblue);

  my $black = $im->colorAllocate(0,0,0);
  my $blue  = $im->colorAllocate(0,0,255);
  
  $im->transparent($bg);
  $im->rectangle(0 + $PADLEFT,0 + $PADTOP,
		 $width - $PADRIGHT,$height - $PADBOTTOM,$black);

  # Set up the axis, grid, and labels
  # Create a suitable scale of y-axis tick marks
  # if drawing 10 up the axis
  my $division = int($biggest_year/10);
  my $total = $division * 10;
  for (my $i = 0;$i<=9;$i++) {
    my $y1 = ($division * $i) * $yscale + $PADBOTTOM;
    print STDERR $division . '-' . $y1,"\n" if DEBUG;
    $im->line(0+$PADLEFT-5,$y1,0 + $PADLEFT,$y1,$black);
    $im->line(0+$PADLEFT,$y1,$width - $PADRIGHT,$y1,$blue);
    $im->string(gdTinyFont,$PADLEFT-20,$y1-(gdTinyFont->height/2),($total - ($division*$i)),$black);
  }

  $im->stringUp(gdSmallFont,0+5,((($height - $PADTOP - $PADBOTTOM)/2) + (gdSmallFont->height * 1)/2),
     'TOTAL ' . uc($class),$black);
  $im->string(gdSmallFont,
	      (($width - $PADLEFT - $PADRIGHT)/2) + ((gdSmallFont->width * 5)/2),$height-20,'YEARS',$black);

  # Now I need to iterate through the range drawing boxes
  # Dynamically set the box width based on the number of years seen
  my $boxwidth = ($width - ($PADLEFT + $PADRIGHT)) / scalar (@$range);
  my $xoffset;
  foreach my $year (@$range) {
    my $total = $years_seen{$year} || 0;
    
    # normalize the yoffset to the biggest column
    my $y1 = int(($height - $PADBOTTOM) - ($total * $yscale));
    my $x1 = int($PADLEFT + $xoffset);
    
    my $x2 = int($x1 + $boxwidth);
    my $y2 = int($height - $PADBOTTOM);
    $im->filledRectangle($x1,$y1,$x2,$y2,$fg);
    $im->rectangle($x1,$y1,$x2,$y2,$black);
    #  $im->stringUp(gdTinyFont,$x1-2,$y2+25,$year,$black);
    
    # Add the year and totals
    $im->stringUp(gdTinyFont,$x1 + ((gdTinyFont->width * 1)/2),$y2+25,$year,$black);
    unless ($omit_totals) {
      $im->stringUp(gdTinyFont,$x1 + ((gdTinyFont->width * 1)/2),$y1-5,$total,$black);
    }
    $xoffset += $boxwidth;
    print STDERR join("\t",$year,$total,$x1,$y1,$x2,$y2),"\n" if DEBUG
  }

  my $string = $im->png;
  return $string;
}

sub library_size {
  my $self = shift;
  my $adaptor = $self->adaptor;
  my $size = $adaptor->query_for_total('filesize');
  my %stats;
  $stats{MB} = sprintf ('%.3f',($size / (1024 * 1024)));
  $stats{GB} = sprintf ('%.3f',($size / (1024 * 1024 * 1024)));
  $stats{Kb} = $size;
  return \%stats;
}

sub library_duration {
  my $self = shift;
  my $adaptor = $self->adaptor;
  my $milliseconds = $adaptor->query_for_total('seconds');
  my $seconds = int($milliseconds / 1000);
  my $days  = _save_remainder($seconds,60 * 60 * 24);
  my $hours = _save_remainder($seconds,60 * 60);
  my $min   = _save_remainder($seconds,60);
   my %stats;
  $stats{days}       = $days;
  $stats{minutes}    = $min;
  $stats{hours}      = $hours;
  $stats{seconds}    = int $seconds;
  $stats{total_time} = join(':',$days,$hours,$min,$seconds);
  return \%stats;
}

sub _save_remainder {
  my ($val) = int($_[0] / $_[1]);
  $_[0] -= $val * $_[1];
  return $val;
}

sub counts {
  my $self = shift;
  my %stats;
  $stats{songs}   = $self->count('songs');
  $stats{artists} = $self->count('artists');
  $stats{albums}  = $self->count('albums');
  $stats{genres}  = $self->count('genres');
  return \%stats;
}



1;