#!C:\Dwimperl\perl\bin\perl.exe
# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;
use CGI::Carp 'fatalsToBrowser';
use CGI qw(:standard);

# Config DB variables
our $platform = "mysql";
our $database = "test";
our $host = "localhost";
our $port = "3306";
our $tablename = "addressbook";
our $user = "root";
our $pw = "password";
our $q = new CGI;
our $message;
our @rows;

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306"; 

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);


#Get the parameter from your html form.
$lname=$q->param('lname');
$fname=$q->param('fname');
$phone=$q->param('phone');
$email=$q->param('email');
$address=$q->param('address');
$zip=$q->param('zip');
$action=$q->param('action');
$lname1=$q->param('lname1');
$fname1=$q->param('fname1');
$phone1=$q->param('phone1');
$email1=$q->param('email1');
$address1=$q->param('address1');
$zip1=$q->param('zip1');

#Subroutines for each user function

sub addcontact{
$sql="INSERT INTO test.addressbook(last_name,first_name,address,zip,email,phone) values('$lname','$fname','$address','$zip','$email','$phone')";
$sth = $connect->prepare($sql)
or die "Can't prepare $sql: $connect->errstrn";
$rv = $sth->execute;
if ($rv == 1){
  $message =  q(Record has been successfully updated!!!);
}else{
  $message =  q(<b style="color:red">Error!!while inserting records</b>);
}
}

sub search{
$sql="SELECT * FROM test.addressbook where last_name='$lname1' and first_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ref = $sth->fetchall_arrayref;
foreach $row (@{$ref}){
    push @rows, @$row;
}
$rows=@rows;
if ($rows<1){
  $message =  q(<b style="color:red">Error! Record not found</b>);
}
return @rows;
}

sub deletecontact{
$sql="DELETE FROM test.addressbook where last_name='$lname1'";
$sth=$connect->prepare($sql);
$rd=$sth->execute;
if ($rd == 1){
  $message =  q(Record has been successfully deleted!!!);
}else{
  $message =  q(<b style="color:red">Error! Couldn't delete record</b>);
}
}

sub update{
$sql="UPDATE test.addressbook SET last_name='$lname1',first_name='$fname1',address='$address1',zip='$zip1',email='$email1',phone='$phone1' WHERE last_name='$lname1' and first_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ru=$sth->execute;
}

# Choose action Add/Search/Delete/Update to call function
if ($action eq "Add"){
    addcontact()
  }
  elsif ($action eq "Search"){
    search()
  } 
  elsif ($action eq "Delete"){
    deletecontact()
  }
  elsif ($action eq "Update"){
    update()
  } 
  else {
    print " Got No parameters";
  } 


# Return to html page
my $URL = '/contactsform.html';
print $q->header( 
 -type=>"text/html");

#Print search results and ask for delete/update
print $q->start_html("Address Book"),
$q->p($message);
if ($action eq "Search"){
print
$q->h3("Search Results"),
$q->p("Last Name: $rows[1]"),
$q->p("First Name: $rows[2]"),
$q->p("Address: $rows[3]"),
$q->p("Zip: $rows[4]"),
$q->p("Phone: $rows[5]"),
$q->p("Email: $rows[6]"),
$q->start_form(),
$q->p(submit(-name =>'action',-value =>'Update'), submit(-name =>'action',-value =>'Delete')),
$q->end_form();
}

#Opens new form for updating entry
if ($action eq "Update"){
print $q->start_form(),		
$q->p("Last Name", textfield(-name  => 'lname1',-value => '$lname',)),
$q->p("First Name", textfield(-name  => 'fname1',-value => '$fname')),
$q->p("Address", textfield(-name  => 'address1',-value => '$address')),
$q->p("Zip", textfield(-name  => 'zip1',-value => '$zip')),
$q->p("Phone", textfield(-name  => 'phone1',-value => '$phone')),
$q->p("Email", textfield(-name  => 'email1',-value => '$email')),
$q->p(submit("Update")),
$q->end_form();
}
print $q->p( qq!<a href="$URL">Back to Main Page</a>! );
print $q->end_html;