#!/usr/bin/perl
use Mysql;
use Image::Info qw(image_info dim);
use Digest::MD5;
use File::MimeInfo::Magic ();
use File::Path;
use POSIX;
use File::NCopy qw(copy);
use Image::Magick;
my $newdbname    = 'travelh';
my $newdbhost    = 'localhost';
my $newdbuser    = 'root';
my $newdbpass    = 'binebine';
my $testdbh      = Mysql->connect( $newdbhost, $newdbname, $newdbuser, $newdbpass ) or die "Break due to: >$Mysql::db_errstr<\n";
my $traveldbname = 'travel';
my $traveldbhost = 'localhost';
my $traveldbuser = 'root';
my $traveldbpass = 'binebine';
my $traveldbh    = Mysql->connect( $traveldbhost, $traveldbname, $traveldbuser, $traveldbpass ) or die "Break due to: >$Mysql::db_errstr<\n";
my $query = "select Hotel.wobjectId, wobject.title, wobject.description from Hotel left join wobject on Hotel.wobjectId=wobject.wobjectid limit 100;";
my $fpsth = $testdbh->query($query) or die " Wrong query: $query: Error: $Mysql::db_errstr";
my $imgdir     = '/var/www/localhost/htdocs/travelh/www/uploads/';
my $imgdestdir = '/home/sorin/S_WORK/eclipse-projects/Travel/root/static/uploads/hotel/';

if ( $fpsth->rows ) {
        while ( my @arref = $fpsth->fetchrow_array ) {
                my $wobjectid    = $arref[0];
                my $hname        = $traveldbh->quote( $arref[1] );
                my $hdescription = $traveldbh->quote( $arref[2] );

                #ne ocupam mai intai de tari - regiuni - orase
                my $lquery = "select taraId, regturisticaId, orasId from Hotel where wobjectId=$wobjectid";
                my $lsth   = $testdbh->query($lquery);
                my ( $tid, $oid, $rid );
                while ( my @lrref = $lsth->fetchrow_array ) {
                        $tid = $lrref[0];
                        $oid = $lrref[2];
                        $rid = $lrref[1];
                }

                #tara
                my $newcid;
                my $newoid;
                my $newrid;
                if ($tid) {
                        my $tquery = "select taraNume, taraSymbol, taraFlaglink, taraMapLink from Tari where taraId=$tid";
                        my $tsth   = $testdbh->query($tquery);
                        my ( $tn, $ts, $fl, $ml );
                        while ( my @trref = $tsth->fetchrow_array ) {
                                $tn = $trref[0];
                                $ts = $trref[1];
                                $fl = $trref[2];
                                $ml = $trref[3];
                        }
                        if ($tn) {
                                $newcid = insertnewcountry( $tn, $ts, $fl, $ml );
                        } else {
                                print "error getting country for $wobjectid";
                        }
                } else {
                        print "Hotelul $wobjectid nu are tara";
                }

                #end tara
                #oras
                if ($oid) {
                        my $tquery = "select orasNume, taraId from Orase where orasId=$oid";
                        my $tsth   = $testdbh->query($tquery);
                        my ( $on, $ot, $fl, $ml );
                        while ( my @trref = $tsth->fetchrow_array ) {
                                $on = $trref[0];
                                $ot = $trref[1];
                        }
                        if ($on) {
                                $newoid = insertnewcity( $on, $ot, $newcid );
                        } else {
                                print "error getting city for $wobjectid";
                        }
                } else {
                        print "Hotelul $wobjectid nu are oras";
                }

                #end oras
                #regiune
                if ($rid) {
                        my $tquery = "select regiuneNume from regiuni where regiuneId=$rid";
                        my $tsth   = $testdbh->query($tquery);
                        my ($rn);
                        while ( my @trref = $tsth->fetchrow_array ) {
                                $rn = $trref[0];
                        }
                        if ($rn) {
                                $newrid = insertnewregion($rn);
                        } else {
                                print "error getting region for $wobjectid";
                        }
                } else {
                        print "Hotelul $wobjectid nu are regiune";
                }

                #end regiune
                #imagini
                my $iquery =
"select image, imagint1, imagint2, imagint3, imagint4, imagint5, imagineext1, imagineext2, imagineext3, imagineext4, imagineext5 from Hotel where wobjectId=$wobjectid";
                my $isth = $testdbh->query($iquery);
                my ( $img, $ii1, $ii2, $ii3, $ii4, $ii5, $ie1, $ie2, $ie3, $ie4, $ie5 );
                while ( my @irref = $isth->fetchrow_array ) {
                        $img = $irref[0];
                        $ii1 = $irref[1];
                        $ii2 = $irref[2];
                        $ii3 = $irref[3];
                        $ii4 = $irref[4];
                        $ii5 = $irref[5];
                        $ie1 = $irref[6];
                        $ie2 = $irref[7];
                        $ie3 = $irref[8];
                        $ie4 = $irref[9];
                        $ie5 = $irref[10];
                }
                print "Inserez imagini:\n $img \n$ii1 \n$ii2 \n$ii3 \n$ii4 \n$ii5 \n$ie1 \n$ie2 \n$ie3 \n$ie4 \n$ie5\n";
                my ( $imgid, $ii1id, $ii2id, $ii3id, $ii4id, $ii5id, $ie1id, $ie2id, $ie3id, $ie4id, $ie5id );
                my $curdir = $imgdir . $wobjectid;
                if ( $img ne "" ) {
                        $imgid = insertimage( $wobjectid, $img );
                        print "EEEEEEEEEEESSSSSSSSTTTTTEEEEEEEEE  $imgid \n";
                } else {
                      $imgid = 'NULL';
                }
                if ( $ii1 ne "" ) {
                        $ii1id = insertimage( $wobjectid, $ii1 );
                } else {
                       $ii1id = 'NULL';
                }
                if ( $ii2 ne "" ) {
                        $ii2id = insertimage( $wobjectid, $ii2 );
                } else {
                       $ii2id = 'NULL';
                }
                if ( $ii3 ne "" ) {
                        $ii3id = insertimage( $wobjectid, $ii3 );
                } else {
                       $ii3id = 'NULL';
                }
                if ( $ii4 ne "" ) {
                        $ii4id = insertimage( $wobjectid, $ii4 );
                } else {
                       $ii4id = 'NULL';
                }
                if ( $ii5 ne "" ) {
                        $ii5id = insertimage( $wobjectid, $ii5 );
                } else {
                       $ii5id = 'NULL';
                }
                if ( $ie1 ne "" ) {
                        $ie1id = insertimage( $wobjectid, $ie1 );
                } else {
                       $ie1id = 'NULL';
                }
                if ( $ie2 ne "" ) {
                        $ie2id = insertimage( $wobjectid, $ie2 );
                } else {
                       $ie2id = 'NULL';
                }
                if ( $ie3 ne "" ) {
                        $ie3id = insertimage( $wobjectid, $ie3 );
                } else {
                       $ie3id = 'NULL';
                }
                if ( $ie4 ne "" ) {
                        $ie4id = insertimage( $wobjectid, $ie4 );
                } else {
                       $ie4id = 'NULL';
                }
                if ( $ie5 ne "" ) {
                        $ie5id = insertimage( $wobjectid, $ie5 );
                } else {
                       $ie5id = 'NULL';
                }

                print "Hotel data: $newcid $newoid $newrid  $imgid $ii1id $ii2id $ii3id $ii4id $ii5id $ie1id, $ie2id, $ie3id, $ie4id, $ie5id\n";

                #end imagini
                #selectam restul din hotel
                my $restq = "select asistentamedicala, seif, facilitatiInternet, roomService, piscina, fitness, sauna, 
                                    spalatorie, telefon, tv, facilitatiHandicapati, parcare, gradinita, facilitatiSportive,
                                    shopping, restaurant, discoteca, bar, fax, paza, adresa, telefonHotel, faxHotel, email, web, stele, numarlocuri 
                             from Hotel where wobjectId = $wobjectid";
                my $reststh = $testdbh->query($restq);
                my (
                      $asistentamedicala,     $seif,         $facilitatiInternet, $roomService,        $piscina,
                      $fitness,               $sauna,        $spalatorie,         $telefon,            $tv,
                      $facilitatiHandicapati, $parcare,      $gradinita,          $facilitatiSportive, $shopping,
                      $restaurant,            $discoteca,    $bar,                $fax,                $paza,
                      $adresa,                $telefonHotel, $faxHotel,           $email,              $web,
                      $stele,                 $numarlocuri
                );

                #we have to quote name and description
                while ( my @restrref = $reststh->fetchrow_array ) {
                        $asistentamedicala     = $restrref[0];
                        $seif                  = $restrref[1];
                        $facilitatiInternet    = $restrref[2];
                        $roomService           = $restrref[3];
                        $piscina               = $restrref[4];
                        $fitness               = $restrref[5];
                        $sauna                 = $restrref[6];
                        $spalatorie            = $restrref[7];
                        $telefon               = $restrref[8];
                        $tv                    = $restrref[9];
                        $facilitatiHandicapati = $restrref[10];
                        $parcare               = $restrref[11];
                        $gradinita             = $restrref[12];
                        $facilitatiSportive    = $restrref[13];
                        $shopping              = $restrref[14];
                        $restaurant            = $restrref[15];
                        $discoteca             = $restrref[16];
                        $bar                   = $restrref[17];
                        $fax                   = $restrref[18];
                        $paza                  = $restrref[19];
                        $adresa                = $restrref[20];
                        $telefonHotel          = $restrref[21];
                        $faxHotel              = $restrref[22];
                        $email                 = $restrref[23];
                        $web                   = $restrref[24];
                        $stele                 = $restrref[25];
                        $numarlocuri           = $restrref[26];

                }
                
#setting values
                        $asistentamedicala = '0' if ($asistentamedicala < 1); 
                        $seif = '0' if ($seif < 1); 
                        $facilitatiInternet = '0' if ($facilitatiInternet < 1);
                        $roomService = '0' if ($roomService < 1);
                        $piscina = '0' if ($piscina < 1);
                        $fitness = '0' if ($fitness < 1);
                        $sauna = '0' if ($sauna < 1);
                        $spalatorie = '0' if ($spalatorie < 1);
                        $telefon = '0' if ($telefon < 1);
                        $tv = '0' if ($tv < 1); 
                        $facilitatiHandicapati = '0' if ($facilitatiHandicapati < 1);
                        $facilitatiSportive = '0' if ($facilitatiSportive < 1);                        
                        $parcare = '0' if ($parcare < 1);
                        $gradinita = '0' if ($gradinita < 1);
                        $shopping = '0' if ($shopping < 1);
                        $restaurant = '0' if ($restaurant < 1);
                        $discoteca = '0' if ($discoteca < 1);
                        $bar = '0' if ($bar < 1);                        
                        $fax = '0' if ($fax < 1);
                        $paza = '0' if ($paza < 1);
                        $stele = '0' if ($stele < 1);
                        $numarlocuri = '0' if ($numarlocuri < 1);                        
               
                
                my $posted  = time();
                my $user    = '1';
                my $dinsert =
"insert into hotel (name, description, file, url, asistentamedicala, seif, facilitatiInternet, roomService, piscina, fitness, sauna, 
                                    spalatorie, telefon, tv, facilitatiHandicapati, parcare, gradinita, facilitatiSportive,
                                    shopping, restaurant, discoteca, bar, fax, paza, imagint1, imagint2, imagint3, 
                                    imagint4, imagint5, imagineext1,imagineext2,imagineext3,imagineext4,imagineext5, 
                                    country, city, region, adresa, telefonHotel, faxHotel, email, web, stele, numarlocuri, posted, user) 
                               values ($hname, $hdescription,$imgid, 
                               \'$web\', $asistentamedicala, 
                               $seif, $facilitatiInternet, $roomService, $piscina, $fitness, $sauna, $spalatorie, 
                               $telefon, $tv, $facilitatiHandicapati, $parcare, $gradinita, $facilitatiSportive, 
                               $shopping, $restaurant, $discoteca, $bar, $fax, $paza, 
                               $ii1id, $ii2id, $ii3id, $ii4id, $ii5id, $ie1id, $ie2id, $ie3id, $ie4id, $ie5id, $newcid, $newrid, $newoid,
                               \'$adresa\', \'$telefonHotel\', \'$faxHotel\', \'$email\', \'$web\', $stele, $numarlocuri, $posted, $user)";
                print $dinsert. "\n";
                $traveldbh->query($dinsert);                
        }
}

sub insertnewcountry {
        my ( $nume, $simbol, $steag, $map ) = @_;
        print "Inserez tara $nume\n";

        #in primul rand vedem daca tara exista deja
        my $query = "select id from country where name = \'$nume\'";
        my $fpsth = $traveldbh->query($query) or die " Wrong query: $query: Error: $Mysql::db_errstr";
        my $cid;
        if ( $fpsth->rows ) {
                while ( my @crref = $fpsth->fetchrow_array ) {
                        $cid = $crref[0];
                }
                print "No need to insert country: $nume\n";
                return $cid;
        } else {
                my $insertq = "insert into country (name, code2) values (\'$nume\', \'$simbol\')";
                $traveldbh->query($insertq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                my $lastinsertedq = "select max(id) from country";
                my $fpsth = $traveldbh->query($lastinsertedq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                if ( $fpsth->rows ) {
                        while ( my @crref = $fpsth->fetchrow_array ) {
                                $cid = $crref[0];
                        }
                }
        }
        return $cid;
}

sub insertnewcity {
        my ( $nume, $taraid, $countryid ) = @_;
        print "Inserez oras $nume\n";

        #in primul rand vedem daca tara exista deja
        my $query = "select id from city where name = \'$nume\'";
        my $fpsth = $traveldbh->query($query) or die " Wrong query: $query: Error: $Mysql::db_errstr";
        my $oid;
        if ( $fpsth->rows ) {
                while ( my @crref = $fpsth->fetchrow_array ) {
                        $oid = $crref[0];
                }
                print "No need to insert city: $nume\n";
                return $oid;
        } else {
                my $insertq = "insert into city (name, country) values (\'$nume\', $countryid)";
                $traveldbh->query($insertq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                my $lastinsertedq = "select max(id) from city";
                my $fpsth = $traveldbh->query($lastinsertedq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                if ( $fpsth->rows ) {
                        while ( my @crref = $fpsth->fetchrow_array ) {
                                $cid = $crref[0];
                        }
                }
        }
        return $cid;
}

sub insertnewregion {
        my ($nume) = @_;
        print "Inserez regiune $nume\n";

        #in primul rand vedem daca tara exista deja
        my $query = "select id from region where name = \'$nume\'";
        my $fpsth = $traveldbh->query($query) or die " Wrong query: $query: Error: $Mysql::db_errstr";
        my $oid;
        if ( $fpsth->rows ) {
                while ( my @crref = $fpsth->fetchrow_array ) {
                        $oid = $crref[0];
                }
                print "No need to insert region: $nume\n";
                return $oid;
        } else {
                my $insertq = "insert into region (name) values (\'$nume\')";
                $traveldbh->query($insertq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                my $lastinsertedq = "select max(id) from region";
                my $fpsth = $traveldbh->query($lastinsertedq) or die " Wrong query: $query: Error: $Mysql::db_errstr";
                if ( $fpsth->rows ) {
                        while ( my @crref = $fpsth->fetchrow_array ) {
                                $cid = $crref[0];
                        }
                }
        }
        return $cid;
}

sub insertimage {
        my ( $wbjid, $image ) = @_;
        my $fimage = $imgdir . $wbjid . '/' . $image;

        #Mai intai, informatiile de care avem nevoie pentru insert
        print "$fimage\n";

        #Dimensiune
        my $info = image_info($fimage);
        my ( $imgw, $imgh ) = dim($info);

        #MD5 idiotic interface
        #        open (MDFILE, "$fimage");
        #        binmode (MDFILE);
        #        my $md5sum = Digest::MD5->new->addfile( MDFILE )->hexdigest;
        my $md5sum = '';

        #Mime type
        my $mimetype = File::MimeInfo::Magic::magic($fimage);
        my ( $contenttype, $contentsubtype ) = split( /\//, $mimetype );
        my $posted = time();
        my $user   = '1';
        my $fsize  = -s $fimage;
        my $iquery = "insert into file (name, posted, user, iwidth, iheight, ctype, cstype, md5, fsize, mnode, snode) 
             values(\'$image\', $posted, $user, $imgw, $imgh, \'$contenttype\', \'$contentsubtype\', 
             \'$md5sum\', \'$fsize\', \'hotel\',\'$wbjid\')";
        my $destinationdir = $imgdestdir . '/' . $wbjid;
        print "Query: " . $iquery . "\n";
        print "Destination: " . $destinationdir . "\n";
        my $answer = thumbnailcopy( $fimage, $destinationdir, $image );
        my $cid;
        $traveldbh->query($iquery) or die " Wrong query: $query: Error: $Mysql::db_errstr";
        my $lastinsertedq = "select max(id) from file";
        my $fpsth         = $traveldbh->query($lastinsertedq) or die " Wrong query: $query: Error: $Mysql::db_errstr";

        if ( $fpsth->rows ) {
                while ( my @crref = $fpsth->fetchrow_array ) {
                        $cid = $crref[0];
                        print "EEEEEEEEEEESSSSSSSSTTTTTEEEEEEEEE  $cid \n";
                }
        }
        print "EEEEEEEEEEESSSSSSSSTTTTTEEEEEEEEE  $cid \n";
        return $cid;
}

sub thumbnailcopy {
        my ( $source, $destdir, $image ) = @_;
        print "Thumbnailcopy\n";
        unless ( -d $destdir ) {
                mkdir($destdir);
        }
        my $destimage = $destdir . '/' . $image;
        copy $source, $destimage;

        #acuma thumbnails pe destimage
        #intai cele patrate
        #cea mica
        my ( $croph, $cropw, $xoffset, $yoffset );
        my $img    = Image::Magick->new;
        my $error  = $img->Read($destimage);
        my $prefix = 'vsthumb-';
        my $xthumb = '20';
        my $ythumb = '20';
        if ( $difx > ( $dify * $tar ) ) {    #Diferenta pe x e mai mare decat diferenta pe y astfel incat il fixam pe y
                my $xfocus = 'center';
                my $yfocus = 'center';
                my ( $x, $y ) = $img->Get( 'width', 'height' );
                my $difx = $x - $xthumb;
                my $dify = $y - $ythumb;
                my $tar  = $xthumb / $ythumb;
                $croph = $y;
                $cropw = int( $y * $tar );

                if ( $xfocus eq 'center' ) {
                        $xoffset = int( ( $x / 2 ) - ( $cropw / 2 ) );
                        $yoffset = 0;
                } elsif ( $xfocus eq 'right' ) {
                        $xoffset = int( $x - $cropw );
                        $yoffset = 0;
                } else {
                        $xoffset = 0;
                        $yoffset = 0;
                }
        } elsif ( $difx < ( $dify * $tar ) ) {    #Diferenta pe y e mai mare decat diferenta pe x il fixam pe x
                $cropw = $x;
                $croph = int( $cropw / $tar );
                if ( $yfocus eq 'center' ) {
                        $yoffset = int( ( $y / 2 ) - ( $croph / 2 ) );
                        $xoffset = 0;
                } elsif ( $yfocus eq 'right' ) {
                        $yoffset = int( $y - $croph );
                        $xoffset = 0;
                } else {
                        $xoffset = 0;
                        $yoffset = 0;
                }
        }
        $img->Crop( width => $cropw, height => $croph, x => $xoffset, y => $yoffset );
        $img->Scale( width => $xthumb, height => $ythumb );
        $error = $img->Write( $destdir . '/' . $prefix . $image );

        #cea mare
        #cea mica
        $img    = Image::Magick->new;
        $error  = $img->Read($destimage);
        $prefix = 'sthumb-';
        $xthumb = '75';
        $ythumb = '75';
        $xfocus = 'center';
        $yfocus = 'center';
        ( $x, $y ) = $img->Get( 'width', 'height' );
        $difx = $x - $xthumb;
        $dify = $y - $ythumb;
        $tar  = $xthumb / $ythumb;

        if ( $difx > ( $dify * $tar ) ) {    #Diferenta pe x e mai mare decat diferenta pe y astfel incat il fixam pe y
                $croph = $y;
                $cropw = int( $y * $tar );
                if ( $xfocus eq 'center' ) {
                        $xoffset = int( ( $x / 2 ) - ( $cropw / 2 ) );
                        $yoffset = 0;
                } elsif ( $xfocus eq 'right' ) {
                        $xoffset = int( $x - $cropw );
                        $yoffset = 0;
                } else {
                        $xoffset = 0;
                        $yoffset = 0;
                }
        } elsif ( $difx < ( $dify * $tar ) ) {    #Diferenta pe y e mai mare decat diferenta pe x il fixam pe x
                $cropw = $x;
                $croph = int( $cropw / $tar );
                if ( $yfocus eq 'center' ) {
                        $yoffset = int( ( $y / 2 ) - ( $croph / 2 ) );
                        $xoffset = 0;
                } elsif ( $yfocus eq 'right' ) {
                        $yoffset = int( $y - $croph );
                        $xoffset = 0;
                } else {
                        $xoffset = 0;
                        $yoffset = 0;
                }
        }
        $img->Crop( width => $cropw, height => $croph, x => $xoffset, y => $yoffset );
        $img->Scale( width => $xthumb, height => $ythumb );
        $error = $img->Write( $destdir . '/' . $prefix . $image );

        #thumbnailul normal
        $img   = Image::Magick->new;
        $error = $img->Read($destimage);
        ( $x, $y ) = $img->Get( 'width', 'height' );
        my $n = '200';
        $prefix = 'thumb-';
        if ( $x > $n || $y > $n ) {
                $r = $x > $y ? $x / $n : $y / $n;
                $img->Scale( width => ( $x / $r ), height => ( $y / $r ) );
        }
        $error = $img->Write( $destdir . '/' . $prefix . $image );

        #thumbnailul mare
        $img   = Image::Magick->new;
        $error = $img->Read($destimage);
        ( $x, $y ) = $img->Get( 'width', 'height' );
        $n      = '350';
        $prefix = 'lthumb-';
        if ( $x > $n || $y > $n ) {
                $r = $x > $y ? $x / $n : $y / $n;
                $img->Scale( width => ( $x / $r ), height => ( $y / $r ) );
        }
        $error = $img->Write( $destdir . '/' . $prefix . $image );

        #imaginea normala
        $img   = Image::Magick->new;
        $error = $img->Read($destimage);
        ( $x, $y ) = $img->Get( 'width', 'height' );
        $n      = '650';
        $prefix = 'vlthumb-';
        if ( $x > $n || $y > $n ) {
                $r = $x > $y ? $x / $n : $y / $n;
                $img->Scale( width => ( $x / $r ), height => ( $y / $r ) );
        }
        $error = $img->Write( $destdir . '/' . $prefix . $image );
}