#!/usr/bin/perl
use DBI;
#use open IO => ':bytes:std';
## DATABASE SETUP - you'll need to change these! ;-)
$work_dir1= "<path>/eprints2/bin";
$database = "Db name";
$user = "db user";
$password = "password";
$host = "localhost";
$port = "3306";
open(FPO, ">$work_dir1/log-subjects") || die ("Unable to output data\n$!\n");


if(!($dbhandle = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port;", $user, $password))) {
  die ("Unable to connect to $database...\n");
} else {
    print "Start processing\n";
#    $sql = "SELECT TRIM(subjectid) FROM subject";
#    @ids = @{$dbhandle->selectcol_arrayref($sql)};
#    sort @ids;
#    %ids_hash = @ids;

    $sql2 = "SELECT DISTINCT TRIM(subjects) FROM archive_subjects";
    @presenti = @{$dbhandle->selectcol_arrayref($sql2)};
    sort @presenti;
    
    $sql3 = 'SELECT * FROM subject WHERE subjectid = ?';
    $sthandle = $dbhandle->prepare($sql3);
    $sql4 = 'SELECT eprintid FROM  archive_subjects WHERE subjects = ?';
    $sth2 = $dbhandle->prepare($sql4);
   
    foreach $dato (@presenti) {
        $sthandle->execute("$dato");
	@rec = $sthandle->fetchrow_array(); 
        $sth2->execute("$dato");
	$numero = @rec;

        if ($numero == 0 ) { 
        #    $sth2->execute("$dato");
	print $dato.': ';
	while ( @rec2= $sth2->fetchrow_array()) { 
	 print $rec2[0].' ';
        }
	print "\n";
	
	}
    }
}