#!/usr/bin/perl -w

use DBI qw(:sql_types); 

#*********************************************** 
#selectionimagelistloader.pl
#
#created on Feb. 2, 2004, 3:30pm
#************************************************

#**********database connections begins here*****
# Use the DBI module  
  
# Declare local variables  
my ($databaseName, $databaseUser, $databasePw, $dbh); 
my ($stmt, $sth); 
                                                          
# Set the parameter values for the connection                                                          
 $databaseName = "DBI:mysql:vibenumeric:localhost";
 $databaseUser = "vibeuser";
 $databasePw   = 'guest'; 
                                                           
# Connect to the database                                                            
# Note this connection can be used to                                                           
# execute more than one statement                                                           
# on any number of tables in the database 
                                      
$dbh = DBI->connect($databaseName, $databaseUser, $databasePw) 
          || die "Connect failed: $DBI::errstr\n"; 
#****end of database*****************************************   

$collectionName ='north';
$inputDir = "./";
$file = "Character_leaf_images.txt";
opendir(SRC, "$inputDir")||die "Can't open dir $inputDir\n";
open(FILE, "$inputDir$file") || die "Can't open file"."$inputDir$file"."\n";

$num_field = 0; #indecate how many fields current row has

while (<FILE>){  #put one line(one row of table) into array of files
  chomp;
  push(@files,$_);
}

foreach (@files){
  foreach (@fields = split "\t", $_){  
    $num_field++;
  } 
  $num_field = 0;
  $fields[0] =~ s/ //g;
  $key= $fields[0];
  ($key1, $tail)=split(/\d/,$key);
   #print "key is $key\n";
  ($key2, $tail)=split(/\./,$fields[0]);
 # print "$key1 and ";
 # print "$key2 ";

  $Character_Group=$fields[1];
  $character_Group_optional=$fields[2];
 # chop($Character_Group);
 # chomp($character_Group_optional);
  $location=$fields[8];
  $value= "$key2";
  $Character_Group=~tr/[A-Z]/[a-z]/;
  $character_Group_optional=~tr/[A-Z]/[a-z]/;
  $midstring='_';
  $path="$Character_Group$midstring$character_Group_optional";
  #print " and $value\n";
  &load_image();
}


sub load_image{
   my $pathid;

   $stmt = "select pathid, xpath from selectionXPath where collection='$collectionName' ";
   #prepare and execute the SQL query                                                        
   $sth = $dbh->prepare($stmt)|| die "prepare: $stmt: $DBI::errstr";                                                       
   $sth->execute || die "execute:$stmt: $DBI::errstr";
   while(@row=$sth->fetchrow_array()){
      $pathid=$row[0];
      $xpath=$row[1];  
      chomp($xpath);
      @x=split(/\</,$xpath);
      $index=@x;
      $str=$x[$index-1];
      @x=split(/\>/,$str);
      print "xpath and path: $x[0] and $str and $path\n";
      if($x[0] eq $path){
         $temppathid=$pathid;
      }
   }
   # Clean up the record set and the database connection  
   $sth->finish();
   if($temppathid ==0){
     $stmt="select MAX(pathid) from selectionXPath";
     #prepare and execute the SQL query   
     $sth = $dbh->prepare($stmt)|| die "prepare: $stmt: $DBI::errstr";
     $sth->execute || die "execute:$stmt: $DBI::errstr";
     $maxpathid = $sth->fetchrow_array();
     $newpathid=$maxpathid +1;
     $sth->finish();
     $stmt = "insert into selectionXPath values('$collectionName', '$xpath', '$location', '$newpathid')";
     #prepare and execute the SQL query
     $sth = $dbh->prepare($stmt)|| die "prepare: $stmt: $DBI::errstr";
     $sth->execute || die "execute:$stmt: $DBI::errstr";
     $sth->finish();
     $temppathid=$newpathid;
   }
   $stmt = "insert into selectionImageList values('$temppathid', '$value', '$location', '0')";         
   #prepare and execute the SQL query
   $sth = $dbh->prepare($stmt)|| die "prepare: $stmt: $DBI::errstr";
   $sth->execute || die "execute:$stmt: $DBI::errstr";
   
   # Clean up the database connection
   $sth->finish();
   
   #$dbh->disconnect();  
}

$dbh->disconnect();

close (FILE);   
closedir(SRC);