#!/usr/bin/perl -w

#
#
# Copyright 2001 Frederic Thirkell
#
 use Tk;                  #appli X
 use DBI;               #pour mysql
 use MP3::Info;     #pour ID3
 use Net::FTP;       #pour FTP

#--------------- Declarations -----------------------------
$adrIP = "192.168.0.2";           #adresse IP de la base de données par défaut
$ipftp = "192.168.0.254";
$ndatabase = "mp3db";            #nom de la base de données par défaut
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dsn1 = "DBI:mysql::$adrIP";       #entete de connexion mysql
$user_name = "root";
$password = "rootfr";

$repdist = "/home/public";
$reploc = "/divx";
$repmp3 = "/home/fred";          #repertoire par defaut pour les fichier mp3
$table = "mp3";                 #table mysql par défaut

#---------------- FENETRE PRINCIPALE --------------------
# Create main window.
my $main = new MainWindow;

#cadre de saisie
$saisie = $main->Frame(-borderwidth=>1);

#cadre pour les boutons
$frameb = $main->Frame(-borderwidth=>1);

#cadre ftp
$frameftp = $main->Frame(-borderwidth=>1);

#un bouton de sortie
$qbutton =   $main->Button(-text => 'Quit',
			-highlightcolor => 'red',
			-activeforeground => 'green',
			-activebackground => 'yellow',
                        -command => sub{exit}
                                              );
#----- Champs de saisie avec label ---------------
$lab1 = $saisie->Label(-text=> "adresse IP Mysql : ");
$ent1 = $saisie->Entry(-textvariable=>\$adrIP);

$lab2 = $saisie->Label(-text=> "nom database : ");
$ent2 = $saisie->Entry(-textvariable=>\$ndatabase);

$lab3 = $saisie->Label(-text=> "repertoire mp3 : ");
$ent3 = $saisie->Entry(-textvariable=>\$repmp3);

$lab4 = $saisie->Label(-text=> "table : ");
$ent4 = $saisie->Entry(-textvariable=>\$table);

$lab5 = $saisie->Label(-text=> "IP FTP : ");
$ent5 = $saisie->Entry(-textvariable=>\$ipftp);

$lab6 = $saisie->Label(-text=> "rep local FTP : ");
$ent6 = $saisie->Entry(-textvariable=>\$reploc);

$lab7 = $saisie->Label(-text=> "rep local FTP : ");
$ent7 = $saisie->Entry(-textvariable=>\$repdist);

#-------- Boutons ------------------------
$qb1 = $frameb->Button(-text => 'Nb fichiers mp3 : ',
                                     -command => \&cpt_mp3
                                      );
$qb2 = $frameb->Button(-text => 'Création database : ',
                                     -command => \&creation_database
                                      );
$qb3 = $frameb->Button(-text => 'Connexion database : ',
                                     -command => \&connect_database
                                      );
$qb4 = $frameb->Button(-text => 'Afichage table : ',
                                     -command => \&affichage_table
                                      );
$qb5 = $frameb->Button(-text => 'Listage table : ',
                                     -command => \&listage_table
                                      );
$qb6 = $frameb->Button(-text => 'Création table mp3 : ',
                                     -command => \&creation_table
                                      );
$qb7 = $frameb->Button(-text => 'Listage champs : ',
                                     -command => \&listage_champs
                                      );
$qb8= $frameb->Button(-text => 'Remplissage base: ',
                                     -command => \&remplissage_base
                                      );

$qb9= $frameftp->Button(-text => 'Connexion FTP : ',
                                     -command => \&connexion_ftp
                                      );
$qb10= $frameftp->Button(-text => 'Tranfert FTP : ',
                                     -command => \&transfert_ftp
                                      );
$qb11= $frameftp->Button(-text => 'ls FTP : ',
                                     -command => \&ls_ftp
                                      );

#-------------------- Interface utilisateur ------------------
$yscroll = $main->Scrolled("Text"); #->pack(-side=>'bottom', -fill=>'both', -expand=>1);


#-------------------- Positionnement ---------------------
$qbutton->grid(-row =>0, -column=>0);

$saisie->grid(-row=>1,-column=>0, -sticky=>"w");

$lab1->grid(-row =>0, -column=>0, -sticky=>"w");
$ent1->grid(-row =>0, -column=>1, -sticky=>"w");

$lab2->grid(-row =>1, -column=>0, -sticky=>"w");
$ent2->grid(-row =>1, -column=>1, -sticky=>"w");

$lab3->grid(-row =>2, -column=>0, -sticky=>"w");
$ent3->grid(-row =>2, -column=>1, -sticky=>"w");

$lab4->grid(-row =>3, -column=>0, -sticky=>"w");
$ent4->grid(-row =>3, -column=>1, -sticky=>"w");

$lab5->grid(-row =>0, -column=>2, -sticky=>"w");
$ent5->grid(-row =>0, -column=>3, -sticky=>"w");

$lab6->grid(-row =>1, -column=>2, -sticky=>"w");
$ent6->grid(-row =>1, -column=>3, -sticky=>"w");

$lab7->grid(-row =>2, -column=>2, -sticky=>"w");
$ent7->grid(-row =>2, -column=>3, -sticky=>"w");

$frameb->grid(-row=>2,-column=>0, -sticky=>"w");

$qb1->grid(-row =>0, -column=>0, -sticky=>"w");
$qb2->grid(-row =>0, -column=>1, -sticky=>"w");
$qb3->grid(-row =>0, -column=>2, -sticky=>"w");
$qb4->grid(-row =>1, -column=>0, -sticky=>"w");
$qb5->grid(-row =>1, -column=>1, -sticky=>"w");
$qb6->grid(-row =>1, -column=>2, -sticky=>"w");
$qb7->grid(-row =>1, -column=>3, -sticky=>"w");
$qb8->grid(-row =>0, -column=>3, -sticky=>"w");

$frameftp->grid(-row=>3,-column=>0, -sticky=>"w");

$qb9->grid(-row =>0, -column=>0, -sticky=>"w");
$qb10->grid(-row =>0, -column=>1, -sticky=>"w");
$qb11->grid(-row =>0, -column=>2, -sticky=>"w");

$yscroll->grid("-","-",-sticky=>"w",-columnspan=>1, -ipadx=>1,-ipady=>1);    #sur 3 colonnes

#------------------  Liaisons -------------------------------------
$ent1->bind('<Return>',[ \&printinfo]);
$ent2->bind('<Return>',[ \&printinfo]);
$ent3->bind('<Return>',[ \&printinfo]);

#------------------------------------------------------------------
# Let Perl/Tk handle window events.
MainLoop;
#----------------------------------------------------

#$file = "Money.MP3";
#$tag = get_mp3tag($file) or die "No TAG info";

#print ("titre : $tag->{TITLE} \n");
#print ("artiste : $tag->{ARTIST} \n");
#print ("album : $tag->{ALBUM} \n");
#print ("annee : $tag->{YEAR} \n");
#print ("commentaire : $tag->{COMMENT} \n");
#print ("genre : $tag->{GENRE} \n");

#$info = get_mp3info ($file) or die "NO info";

#print ("duree : $info->{MM} \n");
#print ("bitrate : $info->{BITRATE} \n");
#print ("time : $info->{TIME} \n");
#print ("frequence : $info->{FREQUENCY} \n");

#system("find /home/fred -name *.[m,M][p,P]3>/home/fred/lmp3.txt");


#---------------------------------- MySQL ----------------------------
#------------------
sub creation_database {
#connexion
$dsn1= "DBI:mysql::$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn1, $user_name, $password, {RaiseError => 1});
#requete
$sth = $dbh ->prepare ("CREATE DATABASE $ndatabase");
$sth ->execute ();
$sth ->finish ();
$dbh ->disconnect ();
}

#---------------------
sub connect_database {
#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});
#requete
$sth = $dbh ->prepare ("SHOW DATABASES");
$sth ->execute ();
#lecture
while (@ary = $sth ->fetchrow_array ())
{
#	print join ("\t", @ary), "\n";
                $yscroll->insert("end","\t");
                $yscroll->insert("end", @ary);
                $yscroll->insert("end","\n");
}
$sth ->finish ();
$dbh ->disconnect ();
}
#---------------------
sub affichage_table {
#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
print ("$dsn");
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});
#requete
$sth = $dbh ->prepare ("SHOW TABLES");
$sth ->execute ();
#lecture
while (@ary = $sth ->fetchrow_array ())
{
#	print join ("\t", @ary), "\n";
                $yscroll->insert("end","\t");
                $yscroll->insert("end", "@ary");
                $yscroll->insert("end","\n");
}
$sth ->finish ();
$dbh ->disconnect ();
}

#---------------------
sub listage_table {
#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});
#requete
$sth = $dbh ->prepare ("SELECT * FROM $table");
$sth ->execute ();
#lecture
while (@ary = $sth ->fetchrow_array ())
{
	print join ("\t", @ary), "\n";
                $yscroll->insert("end", "@ary");
                $yscroll->insert("end","\n");
}
$sth ->finish ();
$dbh ->disconnect ();
}
#---------------------
sub creation_table {
#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});

#requete
$sth = $dbh ->prepare ("CREATE TABLE $table (id int primary key auto_increment,
                                                                                path varchar(120),
                                                                                title varchar(120),
                                                                                artist varchar(120),
                                                                                album varchar(120),
                                                                                genre varchar(20),
                                                                                comment varchar(120),
                                                                                year varchar(4),
                                                                                cb varchar(13))");
$sth ->execute ();

$sth ->finish ();
$dbh ->disconnect ();
}
#---------------------
sub listage_champs {
#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});
#requete
$sth = $dbh ->prepare ("DESCRIBE  $table");
$sth ->execute ();
#lecture
while (@ary = $sth ->fetchrow_array ())
{
	print join ("\t", @ary), "\n";
                $yscroll->insert("end", "@ary");
                $yscroll->insert("end","\n");
}
$sth ->finish ();
$dbh ->disconnect ();
}
#-------- REMPLISSAGE BASE-------------
# utilise le résultat de cpt_mp3
# le principe est de rechercher tous les fichier .mp3 avec la commade find et de remplir un fichier
#à partir de ce fichier pour chaque ligne on fait une commande get_mp3tag et on isole tous les champs
#les champs sont écrits dans un autre fichier qui servira à remplir la base mysql directement à partir du fichier
#/tmp/lmp3.txt : fichier liste de tous les fichiers mp3
#/tmp/lf.txt : fichier avec tous les champs ID3 pour commande mysql "load"

sub remplissage_base {

#ouveture fichier liste des fichiers mp3
if (!open(FH, "/tmp/lmp3.txt")) {                                                               #ouverture fichier
    $yscroll->insert("end", "ERROR: Could not open file1\n");
        return;
        }

#ouverture fichier pour mysql
if (!open(FH1, ">/tmp/lf.txt")) {                                                               #ouverture fichier
    $yscroll->insert("end", "ERROR: Could not open file2\n");
        return;
        }
BOUCLE:
while (<FH>) { $yscroll->insert("end", $_);
        $filemp3 = chomp($_);
#        print ("$_ \n");
#       $filemp3= ~ s/\s/\\ /g;
        $filemp3="$_";
#        print ("$filemp3 \n");
        $tag = get_mp3tag("$_") or break; #die "No TAG info";
        print ("titre : $tag->{TITLE} \n");
        $yscroll->insert("end", "$tag->{TITLE} \n");

#met Unknown dans les champs vides
        $c1 = $tag->{TITLE};
        if ($c1 eq "") { $c1 = "Unknown" ; }
        $c2 = $tag->{ARTIST};
        if ($c2 eq "") { $c2 = "Unknown" ; }
        $c3 = $tag->{ALBUM};
        if ($c3 eq "") { $c3 = "Unknown" ; }
        $c4 = $tag->{GENRE};
        if ($c4 eq "") { $c4 = "Unknown" ; }
        $c5 = $tag->{COMMENT};
        if ($c5  eq "") { $c5 = "Unknown"; }
        $c6 = $tag->{YEAR};
        if ($c6  eq "") { $c6 = "Unknown"; }


        print FH1 "$filemp3\t$c1\t$c2\t$c3\t$c4\t$c5\t$c6\t\n";          #ecriture des champs séparés par une tabulation
        }
close(FH1);

if (!open(FH1, "</tmp/lf.txt")) {                                                               #ouverture fichier
    $yscroll->insert("end", "ERROR: Could not open file3\n");
        return;
        }
#chargement mysql à partie du fichier

#connexion
$dsn = "DBI:mysql:$ndatabase:$adrIP";       #entete de connexion mysql
$dbh = DBI ->connect ($dsn, $user_name, $password, {RaiseError => 1});
#requete
$filesql = "/tmp/lf.txt";
$cmd =  "load data local infile \"$filesql\" into table $table";
print ("$cmd \n");
$sth = $dbh ->prepare ("$cmd");
$sth ->execute ();

close (FH);
close (FH1);

}
#--------------------------FTP -----------------------------------
#--------- connexion ftp ------
sub connexion_ftp{
#connexion ftp sur serveur

$ftp = Net::FTP ->new("$ipftp") or die "ftp impossible : $@\n";
$ftp ->login("fred","fred") or die "login impossible \n";

@rftp = ($ftp->ls);		#recuperation de la commade ls dans un tableau
print "nb lignes du tableau : $#rftp \n";

$res = @rftp[0];
print "$res \n";

my ($i);	#index tableau

for ($i = 0; $i <($#rftp + 1) ; $i++)
{
	print ("@rftp[$i] \n");
                $yscroll->insert("end", "@rftp[$i] \n");
}
}

#--------- transfert ftp ------
sub transfert_ftp{

print "début transfert \n";
$ftp ->put ("/divx/mi2.avi");
print "fin transfert \n";


$ftp->quit();


}

#--------- ls ftp ------
sub ls_ftp{

$ftp->cwd ($repdist);
@rftp = ($ftp->pwd);		#recuperation de la commade pwd dans un tableau
print "nb lignes du tableau : $#rftp \n";

$res = @rftp[0];
print "$res \n";

my ($i);	#index tableau

for ($i = 0; $i <($#rftp + 1) ; $i++)
{
	print ("@rftp[$i] \n");
                $yscroll->insert("end", "@rftp[$i] \n");
}

@rftp = ($ftp->ls);		#recuperation de la commade ls dans un tableau
print "nb lignes du tableau : $#rftp \n";

$res = @rftp[0];
print "$res \n";

my ($i);	#index tableau

for ($i = 0; $i <($#rftp + 1) ; $i++)
{
	print ("@rftp[$i] \n");
                $yscroll->insert("end", "@rftp[$i] \n");
}
}
#-------------------------- AUTRES --------------------------------
#----------------------------
sub printinfo {
print ("$adrIP \n");
$yscroll->insert("end","$adrIP\n");
$yscroll->insert("end","$ndatabase\n");
$yscroll->insert("end","$repmp3\n");
}

#----------------------------
#Création fichier liste des fichiers mp3
#Compter le nombre de fichiers mp3 dans un répertoire

sub cpt_mp3 {
system("find $repmp3 -name '*.MP3'>/tmp/lmp3.txt");      #commande find

system("find $repmp3 -name '*.mp3'>>/tmp/lmp3.txt");

system("wc -l /tmp/lmp3.txt>/tmp/nbmp3.txt");         #comptage, résultat dans fichier
if (!open(FH, "/tmp/nbmp3.txt")) {                                                               #ouverture fichier
    $yscroll->insert("end", "ERROR: Could not open file\n");
        return;
        }
while (<FH>) { $yscroll->insert("end", $_); }
 close (FH);
#$yscroll->insert("end","$repmp3\n");
}