#!/usr/bin/perl
use strict;
use warnings;
use diagnostics;
use DBI;

my $dbh=DBI->connect('DBI:mysql:jobs:localhost', 'e3', '', { RaiseError => 1 })
  or die "Cannot connect: " . $DBI::errstr;

my $counter=0;
my $quiet=0;
my $table='jobs';

count($table) if table_exists($table);

my @reqnos=@ARGV;
if (!@reqnos) { if (-t STDIN) { *ARGV=*DATA; } else { @reqnos=<>; } }

foreach my $reqno (@reqnos) { if ($reqno =~ /^-q$/) { $quiet=1; shift; next; } }

foreach my $reqno (@reqnos) {
  chomp $reqno;
  next unless $reqno;

  #list_jobs() if $reqno =~ /^list$/;

  my($title, $reqno);
  my $stmt=sprintf "SELECT reqno,title FROM %s WHERE reqno='%s'",
    $table, $reqno;

  ($reqno, $title)=reap($stmt);
  insert($reqno, $title) unless $reqno;
}

count($table);
my $t=time-$^T;
printf "Job took %s minutes or %s seconds\n", $t/60, $t;
exit 0;

sub table_exists {
  my $stmt=sprintf "SHOW TABLES like \'%%%s%%\'", $table;

  my @tables=reap($stmt);
  my $tr;
  foreach my $tr (@tables) {
    if ($tr =~ /$table/) {
      printf "Found table %s\n", $tr if !$quiet;
      return $tr;
    }
  }
  return init($table);
}

sub init {
  drop_table($table);

  my $stmt=<<EOT;
    CREATE TABLE $table ( reqno MEDIUMINT NOT NULL AUTO_INCREMENT,
                          title VARCHAR(200),
                          description VARCHAR(50000),
                          star BOOL,
                          midpoint MEDIUMINT,
                          PRIMARY KEY (reqno)
    );
EOT

  my $result=sow($stmt);
  return $result or 1;
}

sub reap {
  my($statement, @rest)=@_;

  printf "reaping: %s\n", $statement if !$quiet;

  my $sth=$dbh->prepare($statement);    # prepare the query
  $sth->execute();                      # execute the query

  my @vals=$dbh->selectrow_array($statement);

  my $e=$dbh->errstr;
  swing_into_action($e) if ($e);

  return @vals;
}

sub harvest {
  my($statement, $loud)=@_;

  #printf "reaping: %s\n",$statement if $loud;

  my $data_ref=$dbh->selectall_arrayref($statement, { 'Columns' => {} });
  if (!$quiet) {
    for my $row (@$data_ref) {
      print "$_ => $row->{$_}\n" for keys %$row;
    }
  }

  my $e=$dbh->errstr;
  swing_into_action($e) if ($e);
  return $data_ref;
}

sub sow {
  my($statement, $loud)=@_;
  printf "sowing %s\n", $statement if $loud;

  my $sthandle=$dbh->prepare($statement) or die $dbh->errstr;
  my $result=$sthandle->execute();

  my $e=$dbh->errstr;
  swing_into_action($e) if ($e);
  return $result;
}

sub swing_into_action {
  my $err=$_;
  if ($err) {
    die $err if ($err =~ /Specified key was too long/);
    warn $err;

    if ( ($err =~ /Table.*doesn't exist/)
      or ($err =~ /Unknown table/)
      or ($err =~ /Unknown column/))
    {
      init($table);
    }
    die $err;
  }
}

sub count {
  my($t, $loud)=@_;
  my $stmt=sprintf 'SELECT COUNT(reqno) FROM %s', $t;
  my @counts=reap($stmt);
  foreach my $count (@counts) {
    next unless $count =~ /^\d+$/;
    printf "%s ids found.\n", $count if $loud;
    return $count;
  }
  return -1;
}

sub insert {
  my($reqno, @descr)=@_;
  die unless $reqno and $reqno =~ /^\d+$/;
  die "$reqno" unless $#descr;

  my $midpoint=length(@descr);

  my $title=shift @descr;
  $title =~ s/^.*<title>//imsx;
  $title =~ s#</title>.*##imsx;
  $title =~ s/^\s+//;    #strip leading spaces
  $title =~ s/\s+$//;    #strip trailing spaces

  $reqno=$dbh->quote($reqno);
  @descr=$dbh->quote(@descr);
  $title=$dbh->quote($title);

  #Placeholders are your friends.
  my $st=sprintf "INSERT INTO %s (reqno,title,descr,midpoint) ", $table;
  my $st1=sprintf "VALUES (%s,%s,%s,%s)", $reqno, $title, @descr, $midpoint;
  $st .= $st1;

  sow($st);

  $st=sprintf "SELECT reqno,title,midpoint FROM %s WHERE reqno=%s",
    $table, $reqno;

  my $sth=$dbh->prepare($st) or die $dbh->errstr;
  $sth->execute();

  ($title,)=reap($st);

  if ($reqno and $title and $reqno and !$quiet) {
    printf "Id: %s \nLen: %s\nTitle: \"%s\"\nUrl: %s\n",
      $reqno, $midpoint, $title, $reqno;
  }

  return 1;
}

sub drop_table {
  my $smt=sprintf "DROP TABLE IF EXISTS %s", $table;
  printf "Dropping table %s\n", $table;
  sow($smt);
  return 0;
}

sub list_jobs {
  my $st="SELECT `reqno`,`midpoint`,`title` FROM " . $table;
  my $loud=1;
  my $dref=harvest($st, $loud);

  for my $row (@$dref) {
    for (keys %$row) {
      $row->{$_} =~ s/^.*<title>//im;
      $row->{$_} =~ s^<title/>.*$^^im;
      print "$_ => $row->{$_}\n" unless $quiet;
    }
  }
  exit 0;
}

sub demur {
  my($msg, $despair_level)=@_;
  printf "%s\n", $msg unless $quiet;
  exit $despair_level or -1;
}

__DATA__
Requisition		# Job Title                           Location

  3753962:      STAR- E-business Sys Consultant 5            Multiple Locations

__END__

  Dear Job Seeker,

  The STAR job agent you created has found the following new Wells Fargo job openings that match your selection criteria.

  Requisition		# Job Title                           Location
 
  3753962:      STAR- E-business Sys Consultant 5            Multiple Locations
 
  To view the details of any postings this job agent found, use the link below to sign on to Wells Fargo's Job Search Home page. In your Job Agent Notifications box, click this search to run it again. The results page includes links to each job description.

From Teamworks:
http://hrpeoplesoft.wellsfargo.com/jobs

From the Internet: 
http://teamworks.wellsfargo.com/jobs

If you would like to refine the search criteria for this job agent, sign on using the link above, then go to the My Saved Searches page and click the Edit link.

Wells Fargo is an Affirmative Action and Equal Opportunity Employer M/F/D/V