#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);
use DBI;


&parsedata; 
@ARGV = split(/\\*\&/, $ENV{'QUERY_STRING'});
$ARGV[0] =~ s/\W//g;

######### when a button is pressed ####################
if ($form{'action'} eq 'imglookup') { &imglookup; }

######### when theres a command in the url ###########
elsif ($ARGV[0] eq 'imglookup') { &imglookup; }

####################
sub imglookup {

print "Content-type: text/html\n\n";

$external = 0;

if (lc($ENV{'SERVER_NAME'}) =~ ".co.uk"){$external = 1};

if ($external eq 1){

$imgloc = "http://$ENV{'SERVER_NAME'}:1589/dhimages";}
else {$imgloc = "http://$ENV{'SERVER_NAME'}/dhimages";}

 

if ( $ARGV[1] ne ""){$stockno = $ARGV[1];}

else {$stockno = "$form{'STOCKNO'}";}

#$stockno = $form{'STOCKNO'};

my $dbs = "DBI:ODBC:DRIVER={SQL Server};SERVER={dms-svr-1};DATABASE={frontiersql}"; 
my ($username, $password) = ('xxx', ''); 
 
 ######################Vehicle details#################
my $dbh = DBI->connect($dbs, $username, $password) or die "$DBI::errstr\n"; 


my $sth = $dbh->prepare('SELECT SalvageVehicles.RegNo, SalvageVehicles.RegYear, Manufacturers.Name, Models.Name, SalvageVehicles.CC, SalvageVehicles.YardNo, SalvageClears.ID, BodyType.ID
FROM ((BodyType RIGHT JOIN SalvageVehicles ON BodyType.ID = SalvageVehicles.BodyTypeID) LEFT JOIN SalvageClears ON SalvageVehicles.ID = SalvageClears.SalvageID) LEFT JOIN (Models LEFT JOIN Manufacturers ON Models.ManuID = Manufacturers.ID) ON SalvageVehicles.ModelID = Models.ID
WHERE (((SalvageClears.ID)='.$stockno.'));') 

or die "Couldn't prepare statement: " . $dbh->errstr;


$sth->execute() or die "execute failed: " . $sth->errstr();

@row = $sth->fetchrow_array();

($reg, $year, $make, $model, $cc, $nsgno, $stocknox, $bodytypeid) = @row;

######################################


##########Body type#################

my $dbh2 = DBI->connect($dbs, $username, $password) or die "$DBI::errstr\n"; 
my $sth2 = $dbh2->prepare('Select [bodytype].[desc] from bodytype where id ='.$bodytypeid.' ;')

or die "Couldn't prepare statement: " . $dbh2->errstr;

$sth2->execute() or die "execute failed: " . $sth2->errstr();

@row2 = $sth2->fetchrow_array();
($bodytype) = @row2;
#############################################

########Sub location################
my $dbh3 = DBI->connect($dbs, $username, $password) or die "$DBI::errstr\n"; 

my $sth3 = $dbh3->prepare('SELECT SubLocations.Name
FROM (SalvageRecoveries RIGHT JOIN (SalvageVehicles LEFT JOIN SalvageClears ON SalvageVehicles.ID = SalvageClears.SalvageID) ON SalvageRecoveries.SalvageID = SalvageVehicles.ID) LEFT JOIN SubLocations ON SalvageRecoveries.SubLocation = SubLocations.ID
WHERE (((SalvageClears.ID)='.$stockno.'));')

or die "Couldn't prepare statement: " . $dbh3->errstr;

$sth3->execute() or die "execute failed: " . $sth3->errstr();

@row3 = $sth3->fetchrow_array();
($sublocation) = @row3;
################################

#######images##################
my $dbh4 = DBI->connect($dbs, $username, $password) or die "$DBI::errstr\n"; 

my $sth4 = $dbh4->prepare('SELECT SalvageVehicles.YardNo, Images.FileType, Images.ID, SalvageClears.ID, Images.DateEntered
FROM Images RIGHT JOIN (SalvageVehicles LEFT JOIN SalvageClears ON SalvageVehicles.ID = SalvageClears.SalvageID) ON Images.SalvageID = SalvageVehicles.ID
WHERE (((Images.FileType)=\'jpg\') AND ((SalvageClears.ID)='.$stockno.'));')



or die "Couldn't prepare statement: " . $dbh4->errstr;

$sth4->execute() or die "execute failed: " . $sth4->errstr();

@row4 = $sth4->fetchrow_array();

($nsgno, $type, $id, $stocknox, $date) = @row4;

#######################################

#########Output###########################

print "<strong>Stock No - $stocknox : $make $model $bodytype $reg NSG Ref - $nsgno - Salvage Rack Location $sublocation  </strong><br>";


while (@row4 = $sth4->fetchrow_array()) {

($nsgno, $type, $id, $stocknox, $date) = @row4;

print "@row4<br>";

@date = split(/-/, $date);

$year = substr($date, 0,4);
$mth = substr($date, 5,2);
$day = substr($date, 8,2);

if ($mth eq "01") {$monnm = "January"};
if ($mth eq "02") {$monnm = "February"};
if ($mth eq "03") {$monnm = "March"};
if ($mth eq "04") {$monnm = "April"};
if ($mth eq "05") {$monnm = "May"};
if ($mth eq "06") {$monnm = "June"};
if ($mth eq "07") {$monnm = "July"};
if ($mth eq "08") {$monnm = "August"};
if ($mth eq "09") {$monnm = "September"};
if ($mth eq "10") {$monnm = "October"};
if ($mth eq "11") {$monnm = "November"};
if ($mth eq "12") {$monnm = "December"};

$sep = '/';
$jpg = "$id.$type";


$imgfile = $imgloc.$sep.$year.$sep.$monnm.$sep.$jpg;

print "<img src=$imgfile border=0 width=400 height=300>";

print "<br>"


}

print "</html>";

$sth->finish(); 
$dbh->disconnect();

$sth2->finish();
$dbh2->disconnect();

$sth3->finish();
$dbh3->disconnect();

$sth4->finish();
$dbh4->disconnect();
}

## Parse data 

sub parsedata {
	$buffer = "";
	read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
	@pairs=split(/&/,$buffer);
	foreach $pair (@pairs)
	{
		@a = split(/=/,$pair);
		$name=$a[0];
		$value=$a[1];
		$value =~ s/\+/ /g;
		$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
		$value =~ s/~!/ ~!/g;
		$value =~ s/[\n\r]/ /sg;
		$value =~ s/\[\]//g;
		push (@data,$name);
		push (@data, $value);
	}
	%form=@data;
	%form;
}
################