#! /usr/bin/perl -w

# todo5.pl - like todo4.pl, but:
# - adds search capability for items

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use WebDB;

#@ MAIN_LOGIC
print header (),
        start_html (-title => "To-Do List", -bgcolor => "white"),
        h2 ("To-Do List");

my $dbh = WebDB::connect ();

# Dispatch to proper action based on user selection

my $choice = lc (param ("choice"));        # get choice, lowercased

if ($choice eq "")                                        # initial script invocation
{
        display_entry_form ();
        display_current_items ($dbh);
}
elsif ($choice eq "submit")
{
        insert_item ($dbh, param ("content"));
        display_entry_form ();
        display_current_items ($dbh);
}
#@ MAIN_DELETE_CLAUSE
elsif ($choice eq "delete")
{
        if (!delete_item ($dbh, param ("id")))
        {
                print p ("No record with id " . param ("id") . " was found");
        }
        display_entry_form ();
        display_current_items ($dbh);
}
#@ MAIN_DELETE_CLAUSE
#@ MAIN_EDIT_CLAUSE
elsif ($choice eq "edit")
{
        if (my $row = select_item ($dbh, param ("id")))
        {
                display_edit_form ($row);
        }
        else
        {
                print p ("No record with id " . param ("id") . " was found");
                display_current_items ($dbh);
        }
}
elsif ($choice eq "update")
{
        update_item ($dbh, param ("id"), param ("content"), param ("status"));
        display_entry_form ();
        display_current_items ($dbh);
}
elsif ($choice eq "cancel")
{
        display_entry_form ();
        display_current_items ($dbh);
}
#@ MAIN_EDIT_CLAUSE
#@ MAIN_SEARCH_CLAUSE
elsif ($choice eq "search")
{
        display_entry_form ();
        display_search_hits ($dbh, param ("content"));
}
#@ MAIN_SEARCH_CLAUSE
else
{
        print p ("Logic error, unknown choice: $choice");
}

$dbh->disconnect ();

print end_html ();
#@ MAIN_LOGIC

exit (0);

# ----------------------------------------------------------------------

# Insert new to-do item if the content is non-empty.

#@ INSERT_ITEM
sub insert_item
{
my ($dbh, $t, $content);

        $t =~ s/^\s+//;        # strip leading whitespace
        $t =~ s/\s+$//;        # strip trailing whitespace

        $content =~ s/^\s+//;        # strip leading whitespace
        $content =~ s/\s+$//;        # strip trailing whitespace
        if ($content ne "")                # if content is non-empty, add to table
        {

               $dbh->do (qq{ INSERT INTO todo SET date = ?, status = "open", content = ?
                          }, undef, $t, $content);




        }
}
#@ INSERT_ITEM

#@ SELECT_ITEM
sub select_item
{
my ($dbh, $id) = @_;
my ($sth, $row);

        $sth = $dbh->prepare (qq{ SELECT * FROM todo WHERE id = ? });
        $sth->execute ($id);
        $row = $sth->fetchrow_hashref ();
        $sth->finish ();
        return ($row);
}
#@ SELECT_ITEM

# Update an item and return the row count (0 if item wasn't found or changed)

#@ UPDATE_ITEM
sub update_item
{
my ($dbh, $id, $content, $status) = @_;

        $content =~ s/^\s+//;        # strip whitespace
        $content =~ s/\s+$//;
        $dbh->do (qq{ UPDATE todo SET content = ?, status = ? WHERE id = ? },
                                undef,
                                $content, $status, $id);
}
#@ UPDATE_ITEM

# Delete an item and return the row count (0 if item wasn't found)

#@ DELETE_ITEM
sub delete_item
{
my ($dbh, $id) = @_;

        return ($dbh->do (qq{ DELETE FROM todo WHERE id = ? },
                                undef,
                                $id));
}
#@ DELETE_ITEM

# Display entry form

#@ DISPLAY_ENTRY_FORM
sub display_entry_form
{
        print strong ("Item Entry Form"),
                start_form (-action => url ()),
                "Date:", br (),
                textarea (-name => "date",
                                        -value => "",
                                        -override => 1,
                                        -rows => 1,
                                        -columns => 15), br (),
                "Things To Do:", br (),
                textarea (-name => "content",
                                        -value => "",
                                        -override => 1,
                                        -rows => 3,
                                        -columns => 80),
                br (),
#@ SEARCH_MOD_AFTER
#@ SEARCH_MOD_BEFORE
                submit (-name => "choice", -value => "Submit"),
#@ SEARCH_MOD_BEFORE
                submit (-name => "choice", -value => "Search"),
#@ SEARCH_MOD_AFTER
                end_form ();
}
#@ DISPLAY_ENTRY_FORM

# Display form for editing item $id.  The content text field and status
# radio buttons are set to their current values.  The id is placed in the
# form as a hidden field so it is transmitted back to the server when
# the form is submitted.

#@ DISPLAY_EDIT_FORM
sub display_edit_form
{
my $row = shift;

        print strong ("Item Editing Form"),
              start_form (-action => url ()),
                "Date:", br (),
                textarea (-name => "date",
                                        -value => "",
                                        -override => 1,
                                        -rows => 1,
                                        -columns => 15), br (),
                "Things To Do:", br (),
                textarea (-name => "content",
                                        -value => "",
                                        -override => 1,
                                        -rows => 3,
                                        -columns => 80),
                br (),
                radio_group (-name => "status",
                                        -values => ["open", "closed", "in-progress"],
                                        -default => $row->{status}),
                br (),
                submit (-name => "choice", -value => "Update"),
                submit (-name => "choice", -value => "Cancel"),
                end_form ();
}
#@ DISPLAY_EDIT_FORM

# If there are any entries, display them, most recent entries first..

#@ DISPLAY_CURRENT_ITEMS
sub display_current_items
{
my $dbh = shift;
my ($sth, $stmt, $count);

        print hr (), strong ("Current items:");
        $stmt = qq { SELECT * FROM todo ORDER BY date DESC  };
        $sth = $dbh->prepare ($stmt);
        $sth->execute ();
        $count = 0;
        while (my $row = $sth->fetchrow_hashref ())
        {
                display_item ($row);
                ++$count;
        }
        $sth->finish ();
        print p ("No items were found") if $count == 0;
}
#@ DISPLAY_CURRENT_ITEMS

# Look for the given string in existing items and display them

#@ DISPLAY_SEARCH_HITS
sub display_search_hits
{
my ($dbh, $str) = @_;
my ($sth, $stmt, $count);

        print hr (), p ("Search Results");
        $str =~ s/^\s+//;        # strip whitespace
        $str =~ s/\s+$//;
        if ($str eq "")                # cancel search if there's nothing to search for
        {
                print p ("No search term was entered");
                return;
        }
        print hr (), strong ("Items matching \"$str\":");
        $stmt = qq { SELECT * FROM todo WHERE content LIKE ? ORDER BY date DESC };
        $sth = $dbh->prepare ($stmt);
        $sth->execute ("%$str%"); # search for wildcard form of string
        $count = 0;
        while (my $row = $sth->fetchrow_hashref ())
        {
                display_item ($row);
                ++$count;
        }
        $sth->finish ();
        print p ("No matching items were found") if $count == 0;
}
#@ DISPLAY_SEARCH_HITS

#@ DISPLAY_ITEM
sub display_item
{
my $row = shift;
my ($edit_url, $delete_url);

        $edit_url = sprintf ("%s?choice=edit;id=%d", url (), $row->{id});
        $delete_url = sprintf ("%s?choice=delete;id=%d", url (), $row->{id});
        print br (), br (),
                        "$row->{t} $row->{status}",
                        " [", a ({-href => $edit_url}, "Edit"), "]",
                        " [", a ({-href => $delete_url}, "Delete"), "]",
                        br (), "\n",
                        "$row->{content}\n";
}
#@ DISPLAY_ITEM



##</code><code>##


sub insert_record
{
my $field_ref = shift;                # reference to field list
my $dbh;
my $stmt;
my @placeholder;
my $rows;

        $dbh = WebDB::connect ();
        $stmt = "";
        @placeholder = ();
        foreach my $f (@{$field_ref})
        {
                next if $f->{value} eq "";                # don't bother with empty fields
                $stmt .= "," if $stmt;                        # put commas between assignments
                $stmt .= $f->{name} . " = ?";        # add column name, placeholder
                push (@placeholder, $f->{value});        # save placeholder value
        }
        $stmt = "INSERT INTO prodreg SET $stmt";        # complete the statement
#@ INSERT_RECORD_CHECK_RESULT
        $rows = $dbh->do ($stmt, undef, @placeholder);
#@ INSERT_RECORD_CHECK_RESULT
        $dbh->disconnect ();

        # display confirmation to customer