#!/usr/bin/perl
use strict;
use DBI;  
use Lingua::EN::NameCase qw( nc );
use File::Copy;

my $targetDir = "./fotokatalogen_sektion_4_v6";
my $imageDir = "./foton";
my $enrolementperiod;
my $addresser = "addresser_2009"
my @pictureTableList = ("nyantagna_2009","nyantagna_2008","nyantagna_2007","nyantagna_2006","nyantagna_2005","nyantagna_2004","nyantagna_2003");

mkdir($targetDir);

#the ODBC name of the database
my $ODBCDataSource = "Namndatabas";

# connects to the database. if this part fails, make sure the database is set up as a System-wide ODBC datasource.
# Google knows how to do it.

my $dbh = DBI->connect("dbi:ODBC:$ODBCDataSource") || die "could not connect to datbabase";
my $n = 4;

my %section;
#Variabeldeklarationer
my $p;
my $sek;
my @program;
my $query;
my $querySektion;
my $query_handle;
my $query_handle_sektion;
while($n != 13){
	#Get programnames
	$query = "SELECT program FROM program WHERE sektion = $n";
	$query_handle = $dbh->prepare($query);
	$query_handle->execute();
	#get sektionsnames
	$querySektion = "SELECT Sektion FROM sektion WHERE ID = '$n'";
	$query_handle_sektion = $dbh->prepare($querySektion);
	$query_handle_sektion->execute();
	$sek = $query_handle_sektion->fetch();
	$query_handle->bind_columns(undef, \$p);
	
	while($query_handle->fetch()) {
		push(@program, $p);
	}
	@{$section{ $n } } = @program;		
	print "@{$sek}: @{$section{$n}}\n";
	undef(@program);
	$n++;
}
my $k;
my $sth;

my $counter= 0;
foreach $k ( keys(%section )) {
    print "$k:\n";
    my @programs = @{$section{$k}};
    mkdir($targetDir."/".$k);
    foreach my $p (@programs) {
		my $sth2 = $dbh->prepare("select programnamn from program where program = '$p'")
			|| print "select programnamn from program where program like '$p%'";
		$sth2-> execute || die "could not fetch data";
		my $pr;
		if( $pr = $sth2->fetchrow_array ) {
		    mkdir($targetDir."/".$k."/".$pr);
		}
		else {
		    die "$p,$pr\n";
		}

		print "$p,$pr\n";
		$sth = $dbh->prepare("select firstname,surname,username,actualyear from $addresser where program = '$p' order by surname,firstname");
		$sth-> execute || die "could not fetch data";

		while(my ($firstname, $surname, $username,$year) = $sth->fetchrow_array ) {
		    $username =~ s/\s+$//;
		    $firstname =~ s/\s+$//;
		    $firstname = nc($firstname);
		    $surname =~ s/\s+$//;
		    $year =~ s/\s+$//;
			
			while(length($year) > 4){
				$enrolementperiod = chop($year);
			}
		    if($enrolementperiod eq "1") {
				$year--;
		    }
			#This need to be year + 1
		    $year -= 2010;
		    print $year;
			$year = abs($year);
			
		    if($year == 0) {
				next;
		    }
		    mkdir($targetDir."/".$k."/".$pr."/".$year);
		    $surname = nc($surname);
		    my $picture;
		    foreach my $t (@pictureTableList) {
				my $sth2 = $dbh->prepare("select picturenumber from $t where username = '$username'")|| print "select picturenumber from $t where username like '$username%'";
				$sth2-> execute || die "could not fetch data";
			        $picture = $sth2->fetchrow_array;

				if($picture ne undef) {
				    last;
				}
		    }
		    $counter++;
		    my $success = 1;
		  #  print "$pr,$firstname,$surname,$username\n";
		    if($picture ne undef || $picture == 0) {
				$picture =~ s/\s+$//;
				$success = copy($imageDir."/".$picture.".jpg",$targetDir."/".$k."/".$pr."/".$year."/".$surname." ".$firstname.".jpg") ||
				copy($imageDir."/".$picture.".JPG",$targetDir."/".$k."/".$pr."/".$year."/".$surname." ".$firstname.".jpg") ||
				copy($imageDir."/".$picture.".jpeg",$targetDir."/".$k."/".$pr."/".$year."/".$surname." ".$firstname.".jpg") ||
				copy($imageDir."/".$picture.".JPEG",$targetDir."/".$k."/".$pr."/".$year."/".$surname." ".$firstname.".jpg");
		    }
		    if(($success == 0 && $! !~ /exists/) ) { 
				open MISSING, "+>>$targetDir/$k/$pr/$year/missing.txt" || die "could not open $targetDir/$k/$pr/$year/missing.txt";
				print MISSING "$surname $firstname\n";
				close MISSING;
				$success = 0;
		    }
		}
    }
}

controlfile("1",$targetDir."/control.txt");
controlfile("2",$targetDir."/control.txt");
controlfile("3",$targetDir."/control.txt");
controlfile("4",$targetDir."/control.txt");
controlfile("5",$targetDir."/control.txt");
controlfile("6",$targetDir."/control.txt");
controlfile("7",$targetDir."/control.txt");
controlfile("8",$targetDir."/control.txt");
controlfile("9",$targetDir."/control.txt");
controlfile("10",$targetDir."/control.txt");
controlfile("11",$targetDir."/control.txt");
controlfile("12",$targetDir."/control.txt");



sub controlfile {
    my ($section,$outfile,@junk) = @_;
    my @results;
    foreach my $p (@{$section{$section}}) {
	my $sth2 = $dbh->prepare("select programnamn from program where program like '$p%'")|| print "select programnamn from program where program like '$p%'";
	$sth2-> execute || die "could not fetch data";	
	if( my $pr = $sth2->fetchrow_array ) {
	    $pr =~ s/\s+$//;	    
	    push(@results,$pr);
	}
    }

    
    my %seen = ();
    my @out = ();
    foreach my $item (@results) {
		unless ($seen{$item}) {
		    # if we get here, we have not seen it before
		    $seen{$item} = 1;
		    push(@out, $item);
		}
    }


    #my $prev = 'nonesuch'; 
    #my @out = grep($_ ne $prev && ($prev = $_), @results); 
    open FD,">>$outfile";
    print FD "$section\t";
    foreach my $p (sort(@out)) {
		print FD "$p\t";	
    }
    print FD "\n";
    close fd;
}

print "$counter\n";