#!/usr/bin/perl

#
# route66_report - A tool to list your route66 database
#
# usage: route66_report
#
# This script must be run in a directory that has either Database.pm in it (i.e., the main route66 directory) or
# a symbolic link to Database.pm.  
#
#
# Please see anders.com for the route66 mp3 playing system
#
# Please report all bugs to Jim Kershner <jrk@wizardskeep.org>
#
#
#    Jim Kershner jrk@wizardskeep.org http://www.wizardskeep.org
#
#    Copyright (c) 2001 Jim Kershner. All rights reserved. This program is
#    free software; you can redistribute it and/or modify it under the
#    terms of the Artistic License, distributed with Perl.


use strict;
use Database;


# main loop 

#open the route66 database
( my $dbh, my $version ) = database_open ();

my $new_page = my $pathid = my $loop_counter =  my $total_songs = my $usernameid = my @genres = my @tmp = my @songs = 0;

my @pathids = select_all_pathids();

$total_songs = @pathids;

    printf "%-25.25s %-25.25s %-25.25s\n", "Artist" , "Album" , "Song";

while ($loop_counter < $total_songs) {
    my ( $filesystemid, $contributorid, $artistid,  $albumid,  $songid,
       $genreid,  $volume,  $timestamp,  $path ) =
       database_get_path ( $dbh, @pathids [$loop_counter] );
    my ( $mood, $tempo, $endsilence, $tracknumber, $year, $song ) =
       database_get_song ( $dbh, $songid );
    my ( $artist ) = database_get_artist ( $dbh, $artistid );
    my ( $artistid, $album, $year ) = database_get_album ( $dbh, $albumid );
    my ( $pref ) = database_get_preference ( $dbh, $pathid, $usernameid );
    

    $loop_counter ++;
    
    printf "%-25.25s %-25.25s %-25.25s\n", $artist , $album , $song;
    

    }


# Subroutines
#
# @pathids = select_all_pathids();
# builds a list from all the songs in your database
#

sub select_all_pathids {

    my @pathids;
    
    my $sql = 0;
    
    $sql = "select distinct paths.pathid from paths";
    
    @pathids = database_raw_sql ( $dbh, $sql );

    return ( @pathids );

}