#!/usr/bin/perl -w

use DBI;
require "/var/www/vhosts/hbcuconnect.com/httpdocs/cgi-bin/commonDB.pl";



sub is_current_member {
  my ($dbh, $sql, $sth, $row, $email);

  $dbh = shift @_;
  $email = shift @_;

  # Check to see if a record exists in registry_data for
  # the specified email address
  $sql = 'select email from registry_data where email=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($email);

  if ( $row = $sth->fetchrow_hashref() ) {
    return 1;
  } else {
    return 0;
  }
}

sub add_minimum_member {
  my ($dbh, $sql, $sth, $email, $password, $major);

  $dbh = shift @_;
  $email = shift @_;
  $password = shift @_;
  $state = shift @_;
  $major = shift @_;

  # Insert a member into the registry_data table with
  # just the information required by non null fields
  $sql = 'insert into registry_data set email=?, password=?, state=?, major=?, signup_date=now()';
  $sth = $dbh->prepare($sql);
  $sth->execute($email, $password, $state, $major) || print $q->header() . $sth->errstr();
  $sth->finish();
}

sub get_member_id {
  my ($dbh, $sql, $sth, $user);

  $dbh = shift @_;
  $email = shift @_;

  $sql = 'select member_id from registry_data where email=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($email);
  $user = $sth->fetchrow_hashref();

  return $user->{'member_id'};
}

sub get_registry_data {
  my ($dbh, $sql, $sth, $email, $user);

  $dbh = shift @_;
  $email = shift @_;

  $sql = 'select * from registry_data where email=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($email);
  $user = $sth->fetchrow_hashref();

  return $user;
}

sub get_resume_data {
  my ($dbh, $sql, $sth, $email, $resume);

  $dbh = shift @_;
  $email = shift @_;

  $sql = 'select * from resume_data where resume_email=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($email);
  $resume = $sth->fetchrow_hashref();

  return $resume;
}

sub save_job_interest {
  my ($dbh, $sth, $sql, $email, $job_id);

  $dbh = shift @_;
  $email = shift @_;
  $job_id = shift @_;

  $sql = 'insert into job_interest set job_id=?, jobseeker_email=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($job_id, $email);
}

sub get_temporary_password {
  return 'hbcu-1234';
}

sub get_job_data {
  my ($dbh, $sql, $sth, $job, $job_id);

  $dbh = shift @_;
  $job_id = shift @_;

  $sql = 'select * from job_listings where job_id=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($job_id);
  $job = $sth->fetchrow_hashref();

  return $job;
}

sub get_employer_data {
  my ($dbh, $sql, $sth, $employer, $employer_id);

  $dbh = shift @_;
  $employer_id = shift @_;

  $sql = 'select * from employer_data where employer_id=?';
  $sth = $dbh->prepare($sql);
  $sth->execute($employer_id);
  $employer = $sth->fetchrow_hashref();

  return $employer;
}

sub prepare_new_member_message {
  my ($password, $message);

  $password = shift @_;

  $message = "Hello new HBCUConnect.com member:\n\nWelcome to HBCUConnect.com!  Please go to the following link to log in and complete your profile in order to fully benefit from the services we provide.  Your temporary password is $password\n\n\thttp://jobs.hbcuconnect.com/cgi-bin/jobSeekerMain.cgi\n\nThank you!\n\nYour Friends at HBCUConnect.com";

  return $message;
}

sub prepare_apply_message {
  my ($job, $employer, $email, $message,
      $name, $state, $phone, $major, $resume, $resume_link);

  $job = shift @_;
  $employer = shift @_;
  $email = shift @_;
  $name = shift @_;
  $state = shift @_;
  $phone = shift @_;
  $major = shift @_;
  $resume = shift @_;
  $resume_link = shift @_;

  $message = "$employer->{'first_name'} $employer->{'last_name'},\n\nThe following individual expressed interest in the job posting $job->{'job_title'}\n\n";

  if ( $name ne '' ) {
    $message .= "Name: $name\n";
  }
  if ( $state ne '' ) {
    $message .= "State: $state\n";
  }
  if ( $phone ne '' ) {
    $message .= "Phone: $phone\n";
  }
  if ( $email ne '' ) {
    $message .= "Email: $email\n";
  }
  if ( $major ne '' ) {
    $message .= "Major: $major\n";
  }

  $message .= "\n";

  if ( ($resume eq '') && ($resume_link eq '') ) {
    $message .= "If you would like to contact this candidate via email, please feel free to reply back to this candidate with any specific instructions on applying to your job listing.\n\n";
  } else {
    if ( $resume_link ne '' ) {
      $message .= "Resume in Microsoft Word format: http://jobs.hbcuconnect.com/cgi-bin/resumes/$resume_link\n\n";
    }
    if ( $resume ne '' ) {
      $message .= "Resume in plain text:\n\n";
      $message .= "$resume\n\n";
    }
  }
  $message .= "Thank you,\n\nHBCUCareerCenter.com";

  return $message;
}

sub save_apply_data {
  my ($dbh, $sql, $sth, $member_id, $job_id, $employer_id);

  $dbh = shift @_;
  $member_id = shift @_;
  $job_id = shift @_;
  $employer_id = shift @_;

  $sql = 'insert into apply_stats set job_id=?, member_id=?, employer_id=?, date_applied=now()';
  $sth = $dbh->prepare($sql);
  $sth->execute($job_id, $member_id, $employer_id);
}

1;