package dggRegistrarData;
use strict;
use warnings;
use DBI;
our $VERBOSE = 0;

#
# Database interface for the RegistrarData example.
# This hides all the messy details of the Perl::DBI and MySQL
# from the rest of the application.
#
# Usage: 
#
#  use RegistrarData;
#  my $data = new RegistrarData;
#  $data->AddPerson( name => "Joe", phone => "111-2222" );
#  while(my $person = $data->getNextPerson){
#    print " name is " . $person->name . "; phone is " . $person->phone . "\n";
#  }
#
# Run test.pl to see if all this works.
#

my $DB_USER = 'dgg';
my $DB_PASS = 'dgg_pa55';
my $DB_DATA = 'dgg_db';
my $dsn     = "DBI:mysql:database=$DB_DATA";

# Connect to the database, 
# and return an object that the caller can use
# to make further database queries.
sub new {
  my $class = shift;
  my $dbh   = DBI->connect( $dsn, $DB_USER, $DB_PASS, {RaiseError=>1}) || '';
  my $self  = { dbh   => $dbh, 
		sth   => '', 
		error => '',
	      };
  return bless($self,$class);
}

# Reset any errors or queries in progress.
sub reset {
  my $self = shift;
  $self->{sth}->finish if $self->{sth};
  $self->{sth}   = '';
  $self->{error} = '';
}

# Return the last database error.
sub getError {
  my $self = shift;
  return $self->{error};
}

# Returns a Person object next person (by name) in the database;
# or returns false if there aren't any more people.
sub getNextPerson {
  my $self = shift;
  my $dbh  = $self->{dbh};
  unless ($dbh){
    $self->{error} = "Oops - no database connection.";
    return '';
  }
  unless ($self->{sth}){
    print " creating sth ... \n" if $VERBOSE;
    eval {
      my $sql = "SELECT * FROM Person ORDER BY Person.name;";
      my $sth = $dbh->prepare($sql);
      print "   sth is prepared ... \n" if $VERBOSE;
      $sth->execute;
      print "   sth is executed ... \n" if $VERBOSE;
      $self->{sth} = $sth;
    };
  }
  print " error is '$@'\n" if $VERBOSE;
  if ($@){ # error in sql processing
    $self->{error} = $@ if $VERBOSE;
    return '';
  };
  return '' unless $self->{sth};
  my $arrayref = $self->{sth}->fetchrow_arrayref;
  if (not $arrayref){
    # no more data - clear stored statement handle (we've seen all students), 
    $self->{sth} = '';
    # and return 'false'
    return '';
  }
  return Person->newFromArrayRef( $arrayref );
}

# Usage: $data->addPerson( name => $name, phone => $phone );
sub addPerson {
  my $self = shift;
  my %args = @_;
  my $dbh  = $self->{dbh};
  unless ($dbh){
    $self->{error} = "Oops - no database connection.";
    return '';
  }
  eval {
    my $sql = "INSERT INTO Person VALUES (NULL, ?, ?);";
    my $sth = $dbh->prepare($sql);
    $sth->execute( $args{name}, $args{phone} );
  };
  if ($@){ # error in sql processing
    $self->{error} = $@ if $VERBOSE;
    return '';
  };
  return 1;
}

# Returns a Course object next course (by name) in the database;
# or returns false if there aren't any more course.
sub getNextCourse {
  my $self = shift;
  my $dbh  = $self->{dbh};
  unless ($dbh){
    $self->{error} = "Oops - no database connection.";
    return '';
  }
  unless ($self->{sth}){
    print " creating sth ... \n" if $VERBOSE;
    eval {
      my $sql = "SELECT * FROM Course ORDER BY Course.title;";
      my $sth = $dbh->prepare($sql);
      print "   sth is prepared ... \n" if $VERBOSE;
      $sth->execute;
      print "   sth is executed ... \n" if $VERBOSE;
      $self->{sth} = $sth;
    };
  }
  print " error is '$@'\n" if $VERBOSE;
  if ($@){ # error in sql processing
    $self->{error} = $@ if $VERBOSE;
    return '';
  };
  return '' unless $self->{sth};
  my $arrayref = $self->{sth}->fetchrow_arrayref;
  if (not $arrayref){
    # no more data - clear stored statement handle (we've seen all students), 
    $self->{sth} = '';
    # and return 'false'
    return '';
  }
  return Course->newFromArrayRef( $arrayref );
}

#Usage: $data->addCourse( title => $title, time => $time, term => $term, place => $place, credits=> $credits );
sub addCourse {
  my $self = shift;
  my %args = @_;
  my $dbh  = $self->{dbh};
  unless ($dbh){
    $self->{error} = "Oops - no database connection.";
    return '';
  }
  eval {
    my $sql = "INSERT INTO Course VALUES (NULL, ?, ?, ?, ?, ?);";
    my $sth = $dbh->prepare($sql);
    $sth->execute( $args{title}, $args{time}, $args{term}, $args{place}, $args{credits} );
  };
  if ($@){ # error in sql processing
    $self->{error} = $@ if $VERBOSE;
    return '';
  };
  return 1;
}


# ==================================================================

package Person;

# Create a new person from an arrayref, 
# which will have the form { arrayref => [id, name, phone] }.
# So after we make one
#   $self->{arrayref}[0] is id
#   $self->{arrayref}[1] is name
#   $self->{arrayref}[2] is phone
sub newFromArrayRef {
  my $class = shift;
  my ($arrayref) = @_;
  my $self = { arrayref => $arrayref };
  return bless($self,$class);
}

# Return the name of this person.
sub name {
  my $self = shift;
  return $self->{arrayref}[1];
}

# Return the phone number of this person.
sub phone {
  my $self = shift;
  return $self->{arrayref}[2];
}


# All packages should return true
1;

#===================================================================

package Course;

# Create a new course from an arrayref,
sub newFromArrayRef {
  my $class = shift;
  my ($arrayref) = @_;
  my $self = { arrayref => $arrayref };
  return bless($self,$class);
}

# Return the title of this course.
sub title {
  my $self = shift;
  return $self->{arrayref}[1];
}

# Return the time of this course.
sub time {
  my $self = shift;
  return $self->{arrayref}[2];
}

# Return the term of this course.
sub term {
  my $self = shift;
  return $self->{arrayref}[3];
}

# Return the place of this course.
sub place {
  my $self = shift;
  return $self->{arrayref}[4];
}

# Return the credits of this course.
sub credits {
  my $self = shift;
  return $self->{arrayref}[5];
}

#returning true
1;
