#!/usr/bin/perl
# showreviews.pl by Sid Sowder (sid@vicious.com)
# version 1.0 1999-JUL-22 by ME

# Run this to go through the database and create an HTML file
# of reviews.  Quick and dirty.

$database = 'sid';
$hostname = 'localhost';
$user     = 'sid';
$password = 'password';
chomp($IP = `cat /home/ssowder/.IP`);			# where is my webserver today?

use DBI;

&getInput;		# did we get any parameters?
&setSQL;		# figure out which select to use
&connectDB;		# connect to the database and run the sql
&headers;		# HTML headers

# Fetch the rows back from the SELECT statement dropping each in a hash
while ( $ref = $sth->fetchrow_hashref() ) {

	$song = "";
	while ( $ref2 = $sth2->fetchrow_hashref() ) {
		$song.= "\t$ref2->{'title'} ".
			"[<a href=\"javascript:play('$ref2->{'playkey'}')\">".
			"mp3</a>]<br />\n";
	}

	$ref->{'artist_nm'} =~ s/(.*), The$/The $1/;

	$ART=0;		#assume the worst

	if (length($ref->{'art_url'}) > 0) {
		$ART = 1;

		unless ($ref->{'art_url'} =~ /http/) {
			$ref->{'art_url'} = 'http://'. $IP .'/albumart/'. $ref->{'art_url'};
		}
	}

	$ref->{'album_nm'} = 's/t' unless ($ref->{'album_nm'});

	# the dB is not consistent 
	if ($ref->{'length_tm'} =~ /:00$/) {
		if ($ref->{'length_tm'} =~ /00:00:00/) {
			$ref->{'length_tm'} = "";
		} else {
			($min, $sec, $msec) = split(':', $ref->{'length_tm'});
			$min=~ s/^0+//;			# remove any leading 0s
			$ref->{'length_tm'} = "$min:$sec";
		}
	} else {
		($hour, $min, $sec) = split(':', $ref->{'length_tm'});
		$hour =~ s/^0+//;			# remove any leading 0s
		if ($hour) {
			$ref->{'length_tm'} = "$hour:$min:$sec";
		} else {
			$ref->{'length_tm'} = "$min:$sec";
		}
	}

	$ref->{'review_tx'} =~ s/\r//g;						# no silly returns
	$ref->{'review_tx'} = &wrap($ref->{'review_tx'});	# wrap as nesc.
	$ref->{'review_tx'} =~ s/<P><\/P>//g;				# avoid blanks

	print "<div style=\"width: 500px;\">\n".
	 "\t<xml title=\"CD Review: $ref->{'artist_nm'} - $ref->{'album_nm'}\">\n".
    "\t<div id=\"albumArt\">\n".
    "\t<img src=\"$ref->{'art_url'}\"><br \\>\n\n".
    "\t<p>\n".
    "\t$ref->{'artist_nm'}<br \\>\n".
    "\t$ref->{'album_nm'}<br \\>\n".
    "\t$ref->{'release_nb'}<br \\>\n".
    "\t$ref->{'label_nm'}<br \\>\n".
    "\t$ref->{'tracks_nb'} tracks<br \\>\n".
    "\t$ref->{'length_tm'}<br \\>\n";

	if ($song) {
		print "\t<br />\n$song";
	}

    if ($FORM{'owner'}) {
        print "\t<p>[<a target=\"new\" href=\"editreview.cgi?id=".
            $ref->{'review_id'} ."\">edit</a>]</p>\n";
    }

    print "\t</p>\n\n".

    "\t</div>\n\n".

    "\t<div id=\"pageContainer\" class=\"pageBlurb\">\n".
    "$ref->{'review_tx'}\n".
    "\t</div>\n".
	"</div>\n";
}

$sth->finish;
$dbh->disconnect;
&footer;
exit;

######################################################################
sub connectDB() {
	$dbh = DBI->connect("DBI:mysql:$database:$hostname",
                         $user, $password) || die "$DBI::errstr\n";

	$sth = $dbh->prepare($sql) 
		|| die "Cannot prepare statement: $DBI::errstr\n";
                                     
	$sth->execute;

	if ($sql2) {
		$sth2 = $dbh->prepare($sql2) 
			|| die "Cannot prepare statement: $DBI::errstr\n";
                                     
		$sth2->execute;
	}
}

sub setSQL() {
	if ($FORM{'letter'}) {
		# JUST A PARTICULAR LETTER OF THE ALPHABET

		$sql = "SELECT review_id, art_url, artist_nm, ".
			"album_nm, label_nm, format_cd, release_nb, tracks_nb, ".
			"length_tm, review_tx FROM review ".
			"WHERE substring(artist_nm,1,1) = '$FORM{'letter'}' ".
			"ORDER BY artist_nm, release_nb";

	} elsif ($FORM{'review_id'}) {
		# Just a particular review 

		$sql = "SELECT review_id, art_url, artist_nm, ".
			"album_nm, label_nm, format_cd, release_nb, tracks_nb, ".
			"length_tm, review_tx ".
			"FROM review ".
			"WHERE review_id = $FORM{'review_id'}";

		# and any songs we may want to share from it
		$sql2 = "SELECT review_id, playkey, title FROM share ".
			"WHERE review_id = $FORM{'review_id'}";

	} elsif ($FORM{'new'} || 1) {
		# NEW REVIEWS (last 28 days) ONLY

		$sql = "SELECT review_id, art_url, artist_nm, ".
			"album_nm, label_nm, format_cd, release_nb, tracks_nb, ".
				"length_tm, review_tx ".
				"FROM review ".
				"WHERE TO_DAYS(NOW())-TO_DAYS(created_dt) <= 28 ".
				"ORDER BY created_dt DESC, artist_nm, release_nb";

	} else {
		# THE WHOLE SHEBANG!
		$sql = "SELECT review_id, art_url, artist_nm, ".
			"album_nm, label_nm, format_cd, release_nb, tracks_nb, ".
				"length_tm, review_tx FROM review ".
				"ORDER BY artist_nm, release_nb";
	}
}

sub wrap {
	$finalline = '';
	@lines = split(/\n/, $_[0]);	

	foreach (@lines) {
		$doneline = '';	
		$finalline .= &cutIt('<P>'.$_);
	} 

	return($finalline);
}

sub cutIt {
	$cwa = 50;					#wrap at 50th char (49th position)
	$line = $_[0];
	$leftover = '';

	print "FULL: $line\n" if ($DEBUG);

	if (length($line) <= $cwa) {
		$doneline .= "\t$line</P>\n";
		print "FINAL BIT: $line\n" if ($DEBUG);
	} else {
		for ($count=$cwa-1;$count>0;$count--) {
			$check = substr($line, $count, 1);
			if ($check =~ /[\s-]/) {
				$break = $count;
				last;

				# $break now contains the position of 
				#the last space or dash
			}
		}

		# For really long lines with no spaces
		$break = $cwa-1 unless ($count);

		$break++ if ($check eq '-');	#keep the dash

		$outline = substr($line,0,$break);
		$doneline .= "\t$outline\n";

		$leftover = substr($line,$break,length($line)-$break);
		chomp($leftover);
		$leftover =~ s/^\s+//;
		&cutIt($leftover) if ($leftover);
	}
	return($doneline);
}

sub getInput {
	if ($ENV{'QUERY_STRING'}) {
		@pairs = split(/&/, $ENV{'QUERY_STRING'});
	} else {
		read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
		@pairs = split(/&/, $buffer);
	}

	foreach $pair (@pairs) {
		($name, $value) = split(/=/, $pair);
		$value =~ tr/+/ /;
		$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
		$value =~ s/~!/ ~!/g;
		$FORM{$name} = $value;
	}
}

sub headers {
print <<EOHN;
Content-type: text/html

<html>
<head>
<title>TMR: </title>
<script language="javascript" src="play.js"></script>
<link rel="StyleSheet" href="featurestyle.css" type="text/css" media="screen">
<!--[if IE 5]><link rel="stylesheet" type="text/css" href="http://new.toomuchroc
k.com/ie.css" title="Site default"><![endif]-->
</head>

<body> 

<div id="header">
   <a href="../index.html"><img class="logo" src="../images/newlogo.gif"
      </a><br />
   <a href="../about.html"><img src="../images/about.gif" width="54"
   height="21" alt="about" /></a><a href="../shows/index.html"><img
   src="../images/accounts.gif" width="193" height="21" alt="live photos
   and accounts" /></a><a href="../features/index.html"><img
   src="../images/features.gif" width="81" height="21" alt="features" /
   ></a><a href="../links.html"><img src="../images/friends.gif"
   width="65" height="21" alt="friends" /></a>
</div>
EOHN
}

sub footer {
print <<EOFN;
<div id="footer">
Too Much Rock<br />
Post Office Box 15526<br />
Chicago Illinois 60657</p>
</div>

</body>
</html>
EOFN
}