#!/usr/bin/perl -w
# Library cgi script
# Requires a backend database, created with create_books.sql
# Written by Brad Marshall <brad@humbug.org.au>

# Modules
use strict;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser carpout);

# Database looks like:
# Title, Author, Series, Volume, Publisher, Date, ISBN

# Variables
my($hostname) = "localhost";
my($user)     = "brad";
my($password) = "";
my($db)       = "library";
my($table)    = "books";
my($cgi)      = "/library/book.pl";
my($dbg)      = 0;
my(@headers)  = ['Title', 'Author', 'Series', 'Volume', 'Publisher', 'Date', 'ISBN'];

my($library) = new CGI;

# Check the action, call the appropriate subroutine
if ($library->param('action') eq "insert") {
    &process_insert;
} elsif ($library->param('action') eq "view") {
    &process_view;
} elsif ($library->param('action') eq "search") {
    &process_search;
} elsif ($library->param('action') eq "delete") {
    &process_delete;
} elsif ($library->param('action') eq "edit") {
    &process_edit;
} elsif ($library->param('action') eq "menu") {
    &process_menu;
} else {
    &process_menu;
}

#### Subroutines go down here ###
# Subroutines for the different actions

sub process_insert {
    if ($library->param('title')) {
        # We have a book, insert it
        my($title) = $library->param('title');
        my($author) = $library->param('author');
        my($series) = $library->param('series');
        my($volume) = $library->param('volume');
        my($publisher) = $library->param('publisher');
        my($date) = $library->param('date');
        my($isbn) = $library->param('isbn');

        print $library->header(-type=>"text/html"),
                $library->start_html(-title=>'Book details', -BGCOLOR=>'white'), "\n";

        print $library->h1('Adding a book');

        &insert_entry($title, $author, $series, $volume, $publisher, $date, $isbn);

        print "Added $title $author $series $volume $publisher $date $isbn<BR>\n";

        print $library->hr;
        print $library->a({-href=>"$cgi?action=insert"}, "Insert another book"), "<BR>\n";
        print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";

        print $library->end_html;

    } else {
        print $library->header(-type=>"text/html"),
              $library->start_html(-title=>'Adding a book', -BGCOLOR=>'white'), "\n";

        print "<FORM METHOD='post' ACTION=\"$cgi\" ENCTYPE='application/x-www-form-urlencoded'>\n";

        print $library->h1('Adding a book');

        print $library->table({-border=>'0'},
                $library->Tr({-align=>'LEFT',-valign=>'CENTER'},[
                    $library->td(["Title: ", $library->textfield(-name=>'title',
                                        -size=>'30')]),
                    $library->td(["Author: ", $library->textfield(-name=>'author',
                                                    -size=>'20')]),
                    $library->td(["Series: ", $library->textfield(-name=>'series',
                                                    -size=>'12')]),
                    $library->td(["Volume: ", $library->textfield(-name=>'volume',
                                                     -size=>'5')]),
                    $library->td(["Publisher: ", $library->textfield(-name=>'publisher',
                                                     -size=>'20')]),
                    $library->td(["Date: ", $library->textfield(-name=>'date',
                                                     -size=>'10')]),
                    $library->td(["ISBN: ", $library->textfield(-name=>'isbn',
                                                     -size=>'15')]),
                 ]
             )
        );

        print $library->hidden(-name=>'action', -value=>'insert');

        print $library->br,
            $library->submit,
            $library->end_form,
            $library->hr,
            $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";
            $library->end_html;
    }
}

sub process_view {
    print $library->header(-type=>"text/html"),
          $library->start_html(-title=>'List of books', -BGCOLOR=>'white'), "\n";

    print $library->h1('Viewing books');

    &view_library;

    print $library->hr;
    print $library->a({-href=>"$cgi?action=menu"}, "Back to menu");

    print $library->end_html;
}

sub process_search {
    if ($library->param('query1value')) {
        print $library->header(-type=>"text/html"),
            $library->start_html(-title=>'Searching books', -BGCOLOR=>'white'), "\n";
        
        print $library->h1("Searching books..");

        my($query) = '';

        if ($library->param('query1value') && !($library->param('query2value')) && $library->param('query3value')) {
            print "Sorry, you must fill these out in order.<BR>\n";
            print $library->end_html;
        } elsif ($library->param('query1value') && $library->param('query2value') && $library->param('query3value')) {
            print "You've entered 3 values.<BR>\n" if $dbg;;
            $query = $library->param('query1type') . " " . $library->param('query1comp') . " \'" . $library->param('query1value') . "\' " . 
                     $library->param('query1op') . " " .
                     $library->param('query2type') . " " . $library->param('query2comp') . " \'" . $library->param('query2value') . "\' " .
                     $library->param('query2op') . " " .
                     $library->param('query3type') . " " . $library->param('query3comp') . " \'" . $library->param('query3value') . "\'";
            print "Query is '$query'<BR>\n" if $dbg;

            &search($query);

            print $library->end_html;
        } elsif ($library->param('query1value') && $library->param('query2value')) {
            print "You've entered 2 values.<BR>\n" if $dbg;;
            $query = $library->param('query1type') . " " . $library->param('query1comp') . " \'" . $library->param('query1value') . "\' " .
                     $library->param('query1op') . " " .
                     $library->param('query2type') . " " . $library->param('query2comp') . " \'" . $library->param('query2value') . "\'";
            print "Query is '$query'<BR>\n" if $dbg;;

            &search($query);

            print $library->end_html;
        } elsif ($library->param('query1value')) {
            print "You've entered 1 value.<BR>\n" if $dbg;;
            $query = $library->param('query1type') . " " . $library->param('query1comp') . " \'" . $library->param('query1value') . "\'";
            print "Query is '$query'<BR>\n" if $dbg;

            &search($query);

            print $library->end_html;
        } else {
            print "You shouldn't be here.<BR>\n";
            print $library->end_html;
        }
    } else {
        print $library->header(-type=>"text/html"),
            $library->start_html(-title=>'Searching books', -BGCOLOR=>'white'), "\n";

        print "<FORM METHOD='post' ACTION=\"$cgi\" ENCTYPE='application/x-www-form-urlencoded'>", "\n";

        print $library->h1("Searching for books");

        print $library->table({-border=>'0'},
                $library->Tr({-align=>'CENTER',-valign=>'CENTER'},[
                    $library->td(
                                    [$library->popup_menu(-name=>'query1type',
                                                      -value=>['title','author','series','volume','publisher','date','isbn'],
                                                      -default=>'title'),
                                     $library->popup_menu(-name=>'query1comp',
                                                      -value=>['LIKE','='],
                                                      -default=>'LIKE'),
                                     $library->textfield(-name=>'query1value',
                                                     -default=>'',
                                                     -size=>'30')
                                    ]
                                ),
                    $library->td({-colspan=>'3'},
                                  [$library->popup_menu(-name=>'query1op',
                                                      -value=>['AND','OR']
                                                     )
                                  ]
                                ), 
                    $library->td(
                                    [$library->popup_menu(-name=>'query2type',
                                                      -value=>['title','author','series','volume','publisher','date','isbn'],
                                                      -default=>'author'),
                                     $library->popup_menu(-name=>'query2comp',
                                                      -value=>['LIKE','='],
                                                      -default=>'LIKE'),
                                     $library->textfield(-name=>'query2value',
                                                     -default=>'',
                                                     -size=>'30')
                                    ]

                                ),
                    $library->td({-colspan=>'3'},
                                  [$library->popup_menu(-name=>'query2op',
                                                      -value=>['AND','OR']
                                                     )
                                  ]
                                ), 
                    $library->td(
                                    [$library->popup_menu(-name=>'query3type',
                                                      -value=>['title','author','series','volume','publisher','date','isbn'],
                                                      -default=>'series'),
                                     $library->popup_menu(-name=>'query3comp',
                                                      -value=>['LIKE','='],
                                                      -default=>'AND'),
                                     $library->textfield(-name=>'query3value',
                                                     -default=>'',
                                                     -size=>'30')
                                    ]

                                ),
                 ]
             )
        );

        print $library->hidden(-name=>'action', -value=>'search');

        print $library->br,
            $library->submit,
            $library->end_form,
            $library->hr,
            $library->end_html;
    }
}

sub process_delete {
    if ($library->param('oid')) {
        print $library->header(-type=>"text/html"),
              $library->start_html(-title=>'Delete Books', -BGCOLOR=>'white'), "\n";

        print $library->h1('Delete books');

        print "we have some oids<BR>\n";
        foreach my $oid ($library->param('oid')) {
            #print "oid = $oid<br>\n";
            &delete_oid($oid);
        }

        print $library->hr;
        print $library->a({-href=>"$cgi?action=delete"}, "Delete another book"), "<BR>\n";
        print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";
    } else {
        print $library->header(-type=>"text/html"),
            $library->start_html(-title=>'Deleting books', -BGCOLOR=>'white'), "\n";

        print "<FORM METHOD='post' ACTION=\"$cgi\" ENCTYPE='application/x-www-form-urlencoded'>", "\n";

        print $library->h1("Deleting books");

        &select_books;

        print $library->hidden(-name=>'action', -value=>'delete');
        print "<input type=submit><br>\n";
        print "</form>\n";
		print $library->hr;
        print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";
        print $library->end_html;
    }
}

sub process_edit {
    if ($library->param('oid')) {
        print $library->header(-type=>"text/html"),
              $library->start_html(-title=>'Edit Books', -BGCOLOR=>'white'), "\n";

        print $library->h1('Edit books');

        print "we have some oids<BR>\n";
        foreach my $oid ($library->param('oid')) {
            #print "oid = $oid<br>\n";
            &edit_oid($oid);
        }

        print $library->hr;
        print $library->a({-href=>"$cgi?action=edit"}, "Edit another book"), "<BR>\n";
        print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";
    } else {
        # Put code here to edit books
        print $library->header(-type=>"text/html"),
              $library->start_html(-title=>'Edit Books', -BGCOLOR=>'white'), "\n";

        print $library->h1('Edit books');

        &select_books;

        print $library->hidden(-name=>'action', -value=>'edit');
        print "<input type=submit><br>\n";
        print "</form>\n";
		print $library->hr;
        print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";
        print $library->end_html;
    }
}

sub process_menu {
    print $library->header(-type=>"text/html"),
        $library->start_html(-title=>'My Library', -BGCOLOR=>'white'), "\n";

    print $library->h1('My library');

    print $library->ul(
        $library->li($library->a({-href=>"$cgi?action=insert"}, "Insert books")),
        $library->li($library->a({-href=>"$cgi?action=delete"}, "Delete books")),
        $library->li($library->a({-href=>"$cgi?action=view"}, "View books")),
        $library->li($library->a({-href=>"$cgi?action=edit"}, "Edit books")),
        $library->li($library->a({-href=>"$cgi?action=search"}, "Search for books")),
    );

	print $library->hr;
    print $library->end_html;
}

### Support subroutines ###

sub search {
	# Subroutine to search the library
    my ($query) = @_;

	my $fullquery = "SELECT * FROM $table WHERE $query";

    print "fullquery = $fullquery<BR>\n" if $dbg;

	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";

	my $sth = $dbh->prepare($fullquery) or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";

    $sth->execute() or die "Can't execute SQL statement: ", $sth->errstr(), "\n";

    my(@rows) = ();

	while( my @ary = $sth->fetchrow_array) {
        push(@rows, $library->td(\@ary));
	}

    if (! @rows ) {
        print "Sorry, no books match<BR>\n";
    } else {
        print $library->table({-border=>'0'},
                          $library->Tr({-align=>'LEFT',-valign=>'CENTER'},
                          [$library->th(@headers), @rows]
                         )
        );
    }

    print $library->hr;
    print $library->a({-href=>"$cgi?action=search"}, "Search again"), "<BR>\n";
    print $library->a({-href=>"$cgi?action=menu"}, "Back to menu"), "<BR>\n";

	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}

sub insert_entry {
	# Subroutine to insert an entry
	my ( $title, $author, $series, $volume, $publisher, $date, $isbn ) = @_;

	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";
	
	my $sth = $dbh->prepare("INSERT INTO $table ( title, author, series, volume, publisher, date, isbn ) VALUES ( ?, ?, ?, ?, ?, ?, ? )");

	$sth->execute( $title, $author, $series, $volume, $publisher, $date, $isbn );

	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}


sub view_library {
	# Subroutine to view the whole library
	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";
	
	my $sth = $dbh->prepare("SELECT * FROM $table");

	$sth->execute();

    my(@rows) = ();
	while( my @ary = $sth->fetchrow_array) {
        #push(@rows, $library->td(\@ary));
        push(@rows, \@ary);
	}

    if (! @rows ) {
        print "Sorry, you have no books entered<BR>\n";
    } else {
        print "<table>\n";
        foreach my $book (sort {$a->[1] cmp $b->[1] || $a->[3] <=> $b->[3]} @rows) {
	
			print "<tr>\n";
			my (@val) = @$book;
			foreach my $entry (@val) {
				print "<td>$entry</td>\n";
			}
			print "</tr>\n";
		}
        print "</table>\n";
    }

	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}

sub select_books {
	# Subroutine to print out a form to select from the whole library

	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";
	
	my $sth = $dbh->prepare("SELECT oid,* FROM $table");

	$sth->execute();

    my(@rows) = ();
	while( my @ary = $sth->fetchrow_array) {
        #push(@rows, $library->td(\@ary));
        push(@rows, \@ary);
	}

    if (! @rows ) {
        print "Sorry, you have no books entered<BR>\n";
    } else {
    	print "<FORM METHOD='post' ACTION=\"$cgi\" ENCTYPE='application/x-www-form-urlencoded'>\n";
        print "<table>\n";
        foreach my $book (sort {$a->[2] cmp $b->[2] || $a->[4] <=> $b->[4]} @rows) {
	
			print "<tr>\n";
			my ($oid, @val) = @$book;
			print "<td><input type=checkbox name=oid value=$oid></td>\n";
			foreach my $entry (@val) {
				print "<td>$entry</td>\n";
			}
			print "</tr>\n";
		}
        print "</table>\n";
    }

	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}

sub edit_oid {
    # Edit a given oid
	my ($oid) = @_;

	print "oid = $oid<BR>\n";
	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";
	
	my $sth = $dbh->prepare("SELECT oid,* FROM $table WHERE oid = $oid");

	$sth->execute();

    my(@rows) = ();
	while( my @ary = $sth->fetchrow_array) {
        #push(@rows, $library->td(\@ary));
        push(@rows, \@ary);
	}

	foreach my $entry (@rows) {
		print "entry = @$entry<BR>\n";
	}
	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}

sub delete_oid {
    # Delete a given oid
	my ($oid) = @_;

	print "Deleting $oid<BR>\n";
	my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", "$user", "$password") or die "Can't connect to postgres db: $!\n";
	
	my $sth = $dbh->prepare("DELETE FROM $table WHERE oid = $oid");

	$sth->execute();
	$sth->finish;

	$dbh->disconnect or warn "Disconnection failed: $!\n";
}

