4406l4.txt

Listing 4: Appointments.pm

package Appointments;

use strict;
use DBI;

# Declare global variables
use vars qw($dbhost $dbuser $dbpassword $dsn @ISA);

my $dbhost = 'localhost';
my $dbuser = 'reuven';
my $dbpassword = '';
my $dsn = "DBI:Pg:dbname=atf;host=$dbhost;";

# We don't inherit from anyone
@ISA = ();

# Constructor: Takes a class as an argument, and 
# connects to the database.  Returns a new 
# Appointments object, or undef if there was
# an error.

sub new
{
    # Get our class
    my $class = shift;

    # Create our instance
    my $self = {};

    # Connect to the database.  Set RaiseError, 
    # but not PrintError, since objects should 
    # not display errors when they occur.
    my $dbh = DBI->connect($dsn, $dbuser, 
                           $dbpassword,
                           {RaiseError => 1, 
                            AutoCommit => 1
                           }
                          );

    # If we could not connect, return undef
    return undef unless (defined $dbh);

    # Store the database handle as an instance 
    # variable
    $self->{dbh} = $dbh;

    # Turn $self into an object
    bless $self, $class;

    # Return the new instance
    return $self;
}

# add_appointment
sub add_appointment
{
    # Get myself
    my $self = shift;

    # Get the parameters
    my ($people, $start_time, 
        $end_time, $notes) = @_;

    # Get the database handle
    my $dbh = $self->{dbh};

    # Make sure that we have everything we need
    return undef unless ($people and $start_time 
                         and $end_time);

    # Create the appropriate SQL                    
    my $sql = "INSERT INTO Appointments ";
    $sql .= " (person_id, start_time, ";
    $sql .= " end_time, notes) ";
    $sql .= " VALUES (?, ?, ?, ?) ";

    # Execute the query
    my $rows_affected =
        $dbh->do($sql, undef, 
                 $people->get_current_person(),
                 $start_time,
                 $end_time, $notes);

    if ($rows_affected)
    {
        return $self;
    }
    else
    {
        return undef;
    }
}

# get_today
sub get_today
{   
  # Get myself
    my $self = shift;

    # Get the database handle
    my $dbh = $self->{dbh};

    # Create SQL for today's appointments
    my $sql = "SELECT P.first_name, P.last_name, ";
    $sql .= "A.start_time, ";
    $sql .= "A.end_time, A.notes ";
    $sql .= "FROM People P, Appointments A ";
    $sql .= "WHERE P.person_id = A.person_id ";
    $sql .= " AND A.start_time > CURRENT_DATE ";
    $sql .= " AND A.end_time < (CURRENT_DATE + 1) ";

    # Prepare and execute the query
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    # Initialize an array of appointments
    my @appointments = ();

    # Retrieve the results, putting them into an 
    # array of hash references
    while (my $hashref = $sth->fetchrow_hashref())
    {
        my %appointment = %{$hashref};
        push @appointments, \%appointment;
    }

    # We're done with this statement
    $sth->finish;

    return @appointments;
}

# get_today_with_person
sub get_today_with_person
{
    # Get myself
    my $self = shift;

    # Get the People object
    my $people = shift;

    # Get the database handle                     
    my $dbh =
    # $self->{dbh};

    # Create SQL for today's appointments
    my $sql = "SELECT P.first_name, P.last_name, ";
    $sql .= "A.start_time, ";
    $sql .= "A.end_time, A.notes ";
    $sql .= "FROM People P, Appointments A ";
    $sql .= "WHERE P.person_id = ? ";
    $sql .= " AND P.person_id = A.person_id ";
    $sql .= " AND A.start_time > CURRENT_DATE ";
    $sql .= " AND A.end_time < (CURRENT_DATE + 1) ";

    # Prepare and execute the query
    my $sth = $dbh->prepare($sql);
    $sth->execute($people->get_current_person());

    # Initialize an array of appointments
    my @appointments = ();

    # Retrieve the results, putting them into an 
    # array of hash references
    while (my $hashref = $sth->fetchrow_hashref())
    {
        my %appointment = %{$hashref}; 
        push @appointments, \%appointment;
    }

    # We're done with this statement
    $sth->finish;

    return @appointments;
}

# Destructor: Called automatically by Perl.  
# We use this to close the database handle.  This 
# isn't really necessary if we are running
# under Apache::DBI.

sub DESTROY
{
    # Get myself
    my $self = shift;

    # Get the database handle
    my $dbh = $self->{dbh};
                            
    # Close the database handle
    $dbh->disconnect;

    return;
}

# Always return a true value from a module
1;