#!/usr/bin/perl -w

$| = 1;

use strict;
use lib "../../libs/";
require "sql-private.pl";

use DBI;
use CGI qw(:standard);
use Text::Template;
use File::Basename;
use POSIX qw(ceil);

use vars qw($scriptname $func $dbh $imgbase $rows $columns);

$scriptname = basename($0);
$imgbase = "http://www.braunglobal.com/fotoalbum/photos";
$rows = 5; # how many rows for the index page
$columns = 3; # how many columns for the index page

defined(param("func")) and $func = param("func") or $func = "";

$dbh = sql_connect();

for ($func) {
    /edit/ and do {edit(); last;};
    /savecomment/ and do {savecomment(); last;};
    /save/ and do {save(); last;};
    /detail/ and do {detail(); last;};
    /index/ and do {indx(); last;};
    // and do {main(); last;};
}

$dbh->disconnect();


########################################################################
# Main Page
########################################################################
sub main {
    my ($sth, $kat_id, $kategorie, $kategorien, $tmpl, $template, $anzahl, %data, $datum, $foldername, $filename, $counter);
    
    $tmpl = "/www/com/braunglobal/www/docs/fotoalbum/templates/main.html";

    $sth = $dbh->prepare("select al_kateg.id,kategorie,count(al_pics.id),al_kateg.datum from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and status like 'enabled' group by kategorie order by al_kateg.datum desc") or die "Preparing: ", $dbh->errstr;
    $sth->execute or die "Executing: ", $sth->errstr;

    $kategorien = "";
    while (($kat_id, $kategorie, $anzahl, $datum) = $sth->fetchrow_array()) {
	$datum =~ /(\d{4})-(\d{2})-(\d{2})\s-\s(\d{4})-(\d{2})-(\d{2})/ and $datum = "$3.$2.$1 - $6.$5.$4"
	or $datum =~ /(\d{4})-(\d{2})-(\d{2})/ and $datum = "$3.$2.$1"
	or $datum =~ /(\d{4})-(\d{2})/ and $datum = "$2.$1";
	
	$kategorien .= "<tr><td><a href='$scriptname?func=index&kat_id=$kat_id'>$kategorie</a></td><td>$datum</td><td align='right'>$anzahl</td></tr>";
    }
    $data{'kategorien'} = $kategorien;
    
    $sth->finish;
    
    # get the top 3
    $sth = $dbh->prepare("select foldername,filename,counter,kategorie,kat_id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and status like 'enabled' order by counter desc limit 3") or die "Preparing: ", $dbh->errstr;
    $sth->execute or die "Executing: ", $sth->errstr;
    ($foldername, $filename, $counter, $kategorie, $kat_id) = $sth->fetchrow_array();
    $data{'top1'} = "<a href='$scriptname?func=detail&kat_id=$kat_id&filename=$filename'><img src='http://www.braunglobal.com/fotoalbum/photos/$foldername/$filename"."k.jpg' border='0'></a>";
    $data{'top1kat'} = "$kategorie<br>$counter";
    
    ($foldername, $filename, $counter, $kategorie, $kat_id) = $sth->fetchrow_array();
    $data{'top2'} = "<a href='$scriptname?func=detail&kat_id=$kat_id&filename=$filename'><img src='http://www.braunglobal.com/fotoalbum/photos/$foldername/$filename"."k.jpg' border='0'></a>";
    $data{'top2kat'} = "$kategorie<br>$counter";

    ($foldername, $filename, $counter, $kategorie, $kat_id) = $sth->fetchrow_array();
    $data{'top3'} = "<a href='$scriptname?func=detail&kat_id=$kat_id&filename=$filename'><img src='http://www.braunglobal.com/fotoalbum/photos/$foldername/$filename"."k.jpg' border='0'></a>";
    $data{'top3kat'} = "$kategorie<br>$counter";
    
    $sth->finish;
    
    $template = new Text::Template (TYPE => 'FILE',  SOURCE => $tmpl) or die "Couldn't construct template: $Text::Template::ERROR";
	
    print "Content-type: text/html\n\n".$template->fill_in(HASH => \%data);

}

########################################################################
# Index Page
########################################################################
sub indx {
    my ($sth, $kat_id, $tmpl, $template, %data, $count, $offset, $filename, $id, $table, $foldername, $pagestring, $anzahl, $i, $beschreibung, $popup);
    
    defined(param("kat_id")) and $kat_id = param("kat_id") or $kat_id = 1;
    defined(param("offset")) and $offset = param("offset") or $offset = 0;
    
    $tmpl = "/www/com/braunglobal/www/docs/fotoalbum/templates/index.html";

    $sth = $dbh->prepare("select kategorie,foldername,count(al_pics.id) from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_kateg.id='$kat_id' and status like 'enabled' group by kategorie order by kategorie") or die "Preparing: ", $dbh->errstr;
    $sth->execute or die "Executing: ", $sth->errstr;
    $count = $sth->rows();
    
    if ($count == 1) {
	($data{'kategorie'}, $foldername, $anzahl) = $sth->fetchrow_array();
    }
    else {
	print "Content-type: text/plain\n\nerror in script\n";
    }
    $sth->finish;

    $sth = $dbh->prepare("select id,filename,beschreibung from al_pics where kat_id = '$kat_id' and status like 'enabled' order by filename limit $offset,".$rows*$columns) or die "Preparing: ", $dbh->errstr;
    $sth->execute or die "Executing: ", $sth->errstr;
    $count = $sth->rows();
    
    if ($count > 0) {
	$table = "";
	$i = 0;
	    
	for (1..$columns) {
	    $table .= "<!--table border='0' cellpadding='10' cellspacing='0'--><tr>\n";
	    for (1..$rows) {
		$i >= $count and last;
		
		($id, $filename, $beschreibung) = $sth->fetchrow_array();
				
		$beschreibung ne "" and $popup = " onMouseOver=\"return overlib('$beschreibung')\" onMouseOut=\"nd()\"" or $popup = "";
		
		$table .= "  <td align='center' valign='middle'>";
		(-e "/www/com/braunglobal/www/docs/fotoalbum/photos/$foldername/$filename"."k.jpg") and $table .= "<a href='$scriptname?func=detail&amp;filename=$filename&amp;kat_id=$kat_id&amp;offset=$offset'$popup><img src='$imgbase/$foldername/$filename"."k.jpg' border='0'></a>" or $table .= "<img src='$imgbase/../templates/picturemissing.gif' border='0'>";
		$table .= "</td>\n";
		$i++;
	    }
	    $table .= "</tr><!--/table-->\n";
	}
	
	$pagestring = "&nbsp;";
	$anzahl = ceil($anzahl / ($rows * $columns));
	for ($i = 1; $i <= $anzahl; $i++) {
            if (($i-1)*15 == $offset) {
	        $pagestring .= "$i&nbsp;";
	    }
            else {
	        $pagestring .= "<a href='$scriptname?func=index&amp;kat_id=$kat_id&amp;offset=".(($i-1)*15)."'>"."[$i]</a>&nbsp;";
            }
	}
	$data{'pagestring'} = $pagestring;
	$data{'tabelle'} = $table;
	
    }
    else {
	print "Content-type: text/plain\n\nerror in script\n";
    }
    
    $sth->finish;
    
    $template = new Text::Template (TYPE => 'FILE',  SOURCE => $tmpl) or die "Couldn't construct template: $Text::Template::ERROR";
	
    print "Content-type: text/html\n\n".$template->fill_in(HASH => \%data);
	
}

########################################################################
# Show single picture
########################################################################
sub detail {
    my ($filename, $sth, %data, $tmpl, $template, $kategorie, $beschreibung, $text, $pict, $imgurl, $date, $count, $foldername, $kat_id, $offset, $go, $statement, $counter, $pic_id);
        
    $data{'scriptname'} = $scriptname;

    defined(param("filename")) and $filename = param("filename") or $filename = "";
    defined(param("go")) and $go = param("go") or $go = "";
    
    $kat_id = param("kat_id");
    defined(param("offset")) and $offset = param("offset") or $offset = 0;
        
    if ($filename ne "") {

	if ($go eq "next") {
	    $statement= " al_pics.filename > '$filename' order by filename ";
	}
	elsif ($go eq "prev") {
	    $statement= " al_pics.filename < '$filename' order by filename desc ";
	}
	else {
	    $statement= " al_pics.filename = '$filename' order by filename ";
	}
	$sth = $dbh->prepare("select kat_id,kategorie,filename,beschreibung,text,al_pics.datum,foldername,counter,al_pics.id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_kateg.id = '$kat_id' and status like 'enabled' and $statement limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	
	if ($count == 1) {
	    ($data{'kat_id'}, $data{'kategorie'}, $data{'filename'}, $data{'beschreibung'}, $data{'text'}, $data{'date'}, $foldername, $counter, $pic_id) = $sth->fetchrow_array();
	    $data{'imgurl'} = $imgbase."/".$foldername."/".$data{'filename'}.".jpg";
	    $data{'counter'} = $counter;
	    $data{'date'} ne "0000-00-00" and $data{'date'}= sqldate2date($data{'date'}) or $data{'date'} = "&nbsp;";
	}
	
	$sth->finish;
	
	$data{'back'} = "index.cgi?func=index&kat_id=$kat_id&offset=$offset";
	
	$sth = $dbh->prepare("select al_pics.id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_pics.filename > '$data{'filename'}' and status like 'enabled' order by filename limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	$sth->finish;
	
	$count == 1 and $data{'next'} = "<a href='index.cgi?func=detail&kat_id=$kat_id&offset=$offset&filename=$data{'filename'}&go=next' onMouseOver=\"return overlib('next picture', DELAY, 500)\" onMouseOut=\"nd()\"><img src='img/next.gif' border='0'></a>" or $data{'next'} = "";

	$sth = $dbh->prepare("select al_pics.id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_pics.filename < '$data{filename}' and status like 'enabled' order by filename limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	$sth->finish;
	
	$count == 1 and $data{'prev'} = "<a href='index.cgi?func=detail&kat_id=$kat_id&offset=$offset&filename=$data{'filename'}&go=prev' onMouseOver=\"return overlib('previous picture', DELAY, 500)\" onMouseOut=\"nd()\"><img src='img/previous.gif' border='0'></a>" or $data{'prev'} = "";
	
	if ($data{'text'} eq "") {
	    $tmpl = "/www/com/braunglobal/www/docs/fotoalbum/templates/photo.html";
	}
	else {
	    $tmpl = "/www/com/braunglobal/www/docs/fotoalbum/templates/phototext.html";
	}
	$template = new Text::Template (TYPE => 'FILE',  SOURCE => $tmpl) or die "Couldn't construct template: $Text::Template::ERROR";
	
	# select all comments for this picture
	my ($md, $comment, $comments);
	$data{'comments'}= "<table width='' cellpadding='4' cellspacing='0' border='0'>\n";
        $sth = $dbh->prepare("select DATE_FORMAT(md, '%d.%m.%Y %H:%i'),comment from al_kommentare where pic_id = $pic_id order by md desc") or die "Preparing: ", $dbh->errstr;
        $sth->execute or die "Executing: ", $sth->errstr;
	while (($md, $comment) = $sth->fetchrow_array()) {
		$data{'comments'} .= "<tr><td>$md</td><td>$comment</td></tr>\n";
	}
        $sth->finish;
	
	$data{'comments'} .= "</table>\n";
	$data{'pic_id'} = $pic_id;
	$data{'redirect'} = "$scriptname?func=detail&amp;filename=$filename&amp;kat_id=$kat_id&amp;go=$go";


	print "Content-type: text/html\n\n".$template->fill_in(HASH => \%data);
	
	# update counter
	$sth = $dbh->prepare("update al_pics set counter = counter + 1, lastip = '".$ENV{'REMOTE_ADDR'}."' where kat_id = '$kat_id' and filename like '$filename' and lastip not like '".$ENV{'REMOTE_ADDR'}."'") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$sth->finish;
	
	
    }
    else {
	print "Content-type: text/plain\n\nerror in script\n";
    }
}

########################################################################
# Save Comment
########################################################################
sub savecomment {
	my ($sth, $pic_id);
	my ($redirect, $comment);

	defined(param("redirect")) and $redirect = param("redirect");
	defined(param("comment")) and $comment = param("comment") or $comment = "";
	defined(param("pic_id")) and $pic_id = param("pic_id") or $pic_id = 0;

	$comment =~ s/<.*?>//g;
	$comment =~ s/['"]//g;

	$sth = $dbh->prepare("insert into al_kommentare set pic_id='$pic_id', comment='$comment'") or die "Preparing: ", $dbh->errstr;
        $sth->execute or die "Executing: ", $sth->errstr;
        $sth->finish;

	print "Status: 302 redirect\nLocation: $redirect\n";
}


########################################################################
# Edit single picture
########################################################################
sub edit {
    my ($filename, $sth, %data, $tmpl, $template, $kategorie, $beschreibung, $text, $pict, $imgurl, $date, $count, $foldername, $kat_id, $offset, $go, $statement, $status);
        
    defined(param("filename")) and $filename = param("filename") or $filename = "";
    defined(param("go")) and $go = param("go") or $go = "";
    
    $kat_id = param("kat_id");
    defined(param("offset")) and $offset = param("offset") or $offset = 0;
        
    if ($filename ne "") {

	if ($go eq "next") {
	    $statement= " al_pics.filename > '$filename' order by filename ";
	}
	elsif ($go eq "prev") {
	    $statement= " al_pics.filename < '$filename' order by filename desc ";
	}
	else {
	    $statement= " al_pics.filename = '$filename' order by filename ";
	}
	$sth = $dbh->prepare("select kat_id,kategorie,filename,beschreibung,text,al_pics.datum,foldername,status from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_pics.kat_id = '$kat_id' and $statement limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	
	if ($count == 1) {
	    ($data{'kat_id'}, $data{'kategorie'}, $data{'filename'}, $data{'beschreibung'}, $data{'text'}, $data{'date'}, $foldername, $status) = $sth->fetchrow_array();
	    $data{'imgurl'} = $imgbase."/".$foldername."/".$data{'filename'}.".jpg";
	    $data{'date'} = sqldate2date($data{'date'});
	}
	
	$sth->finish;
	
	if ($status eq "enabled") {
	    $data{'enabled'} = "selected";
	    $data{'disabled'} = "";
	}
	else {
	    $data{'enabled'} = "";
	    $data{'disabled'} = "selected";
	}
	
	$data{'back'} = "index.cgi?func=index&kat_id=$kat_id&offset=$offset";
	
	$sth = $dbh->prepare("select al_pics.id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_pics.filename > '$data{'filename'}' order by filename limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	$sth->finish;
	
	$count == 1 and $data{'next'} = "<a href='index.cgi?func=detail&kat_id=$kat_id&offset=$offset&filename=$data{'filename'}&go=next'><img src='img/next.gif' border='0'></a>" or $data{'next'} = "";

	$sth = $dbh->prepare("select al_pics.id from al_kateg,al_pics where al_kateg.id = al_pics.kat_id and al_pics.filename < '$data{filename}' order by filename limit 1") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$count = $sth->rows();
	$sth->finish;
	
	$count == 1 and $data{'prev'} = "<a href='index.cgi?func=detail&kat_id=$kat_id&offset=$offset&filename=$data{'filename'}&go=prev'><img src='img/previous.gif' border='0'></a>" or $data{'prev'} = "";
	
	$tmpl = "/www/com/braunglobal/www/docs/fotoalbum/templates/photoedit.html";
	$template = new Text::Template (TYPE => 'FILE',  SOURCE => $tmpl) or die "Couldn't construct template: $Text::Template::ERROR";
	
	print "Content-type: text/html\n\n".$template->fill_in(HASH => \%data);
	
    }
    else {
	print "Content-type: text/plain\n\nerror in script\n";
    }
}

########################################################################
# Save data
########################################################################
sub save {
    my ($sth, $kat_id, $filename, $datum, $beschreibung, $text, $status);
    
    $kat_id = param("kat_id");
    $filename = param("filename");
    $datum = param("datum");
    $beschreibung = param("beschreibung");
    $text = param("text");
    $status = param("status");
    
    $text =~ s/\'/\\\'/g;
    
    if ($kat_id ne "" and $filename ne "" and $datum =~ /(\d{1,2})\.(\d{1,2})\.(\d{4})/) {
	
	$datum = "$3-$2-$1";
	
	$sth = $dbh->prepare("update al_pics set beschreibung = '$beschreibung', datum = '$datum', text = '$text', status = '$status' where filename like '$filename' and kat_id = '$kat_id'") or die "Preparing: ", $dbh->errstr;
	$sth->execute or die "Executing: ", $sth->errstr;
	$sth->finish;
    
	print "Location: http://www.braunglobal.com/fotoalbum/index.cgi?func=edit&kat_id=$kat_id&filename=$filename&go=next\n\n";
    }
    else {
	print "Content-type: text/plain\n\nerror in script\n";
    }
}


########################################################################
# Utility Methods
########################################################################
sub sqldate2date {
    $_[0] =~ /^(\d{4}).??(\d{2}).??(\d{2})/;
    "$3.$2.$1";
}