#!/usr/bin/perl

print "<?xml version=\"1.0\" encoding=\"utf-8\"?>
<ROOT>
";

use File::Copy;
use DBI;
 $dbh = DBI->connect('dbi:mysql:fisd_stormtrooperforums','fisd_fisd','Sh!nyWhite')  or die "Connection Error: $DBI::errstr\n";
 $sql = "select * from newnewvwEIB ORDER BY EIB_GrantedDate";
 $sth = $dbh->prepare($sql);
 $sth->execute
 or die "SQL Error: $DBI::errstr\n";

 print "<!--##############\n";
 print "# Do not edit this xml manually.\n";
 print "# It is computer generated at midnight,\n";
 print "# Any changes you make will be lost!\n";
 print "# Instead, make the changes for each\n";
 print "# trooper in the database. --!>\n";
 while (@row = $sth->fetchrow_array) {
	$row[12] =~ s/\n//;
	#Strip " from all entries as it breaks the XML /Locitus
	$userID = $row[10];
	$userID =~ s/"//;
	$tkid = $row[4];
	$tkid =~ s/"//;
	$name = $row[12];
	$name =~ s/"//;
	$garrison = $row[6];
	$garrison =~ s/"//;
	$country = $row[5];
	$country =~ s/"//;
	$label = $row[13];
	$label =~ s/"//;
	$centDate = $row[9];
	$centDate =~ s/"//;
	$eibDate = $row[8];
	$eibDate=~ s/"//;
	
	print "<trooper
	userID=\"$userID\"
	tkid=\"$tkid\"
	name=\"$name\"
	garrison=\"$garrison\"
	country=\"$country\"\n";
	print "	label=\"$label\"\n" if $label;
	print "	centurionGrantedDate=\"$centDate\"\n" if $centDate;
	print "	statusGrantedDate=\"$eibDate\"
        cohort=\"";

#a=ANH|e=ESB|r=ROTJ|h=HWT|c=TKC|i=ICN
	$cohort = $row[11];
	$cohort =~ s/^\|//;
	$cohort =~ s/\|$//;
	$cohort =~ tr/\||/,/;
	$cohort =~ s/a/ANH/;
	$cohort =~ s/e/ESB/;
	$cohort =~ s/r/ROTJ/;
	$cohort =~ s/h/HWT/;
	$cohort =~ s/c/TKC/;
	$cohort =~ s/i/ICN/;
#@cohorts = split(/\|/,$row[11]);

#$cohorts = join(",",@cohorts);

print "$cohort\"\n
/>

";

#Copy placeholder picture unless a real picture exists. -Locitus
$thumbnailpic = "./thumbnailpix/$row[4]\.jpg";
$profilepic = "./profilepix/$row[4]\ profile.jpg";
unless (-e $thumbnailpic){
	$filetobecopied = "./thumbnailpix/XXXX.jpg";
	$newfile = "$thumbnailpic";
	copy($filetobecopied, $newfile) or die "File cannot be copied.";
 } 
unless (-e $profilepic){
	$filetobecopied = "./profilepix/XXXX\ profile.jpg";
	$newfile = "$profilepic";
	copy($filetobecopied, $newfile) or die "File cannot be copied.";
 } 

}
print " </ROOT>\n";
