1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
|
#!/usr/bin/perl
use DBI;
$extract_dir = "/var/cvtheque";
$log_dir = "/var/cvtheque";
my $dbh = DBI->connect('DBI:mysql:mabase', 'monuser', 'monpass') or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare("SELECT concat(rc.name,' ',replace(rc.surname,',',' ')) candidat,
a.data,
a.origine,
case a.typemime when 'application/pdf' then 'pdf'
when 'text/plainf' then 'txt'
when 'text/html' then 'html'
when 'text/richtext' then 'rtf'
else 'doc' end filetype
FROM (SELECT rc.idrc,rcv.data,rcv.typemime,rcv.origine
FROM rec_candidat rc,
rec_cv rcv,
rec_lien_candidat_cv rl
WHERE rcv.idrcv = rl.idrcv
AND rl.idrc = rc.idrc) a,
(SELECT rc.idrc,max(rcv.origine) max_date
FROM rec_candidat rc,
rec_cv rcv,
rec_lien_candidat_cv rl
WHERE rcv.idrcv = rl.idrcv
AND rl.idrc = rc.idrc
GROUP BY rc.idrc) b,
rec_candidat rc
WHERE a.idrc=b.idrc
AND a.origine=b.max_date
AND a.idrc=rc.idrc") or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute;
open(my $lh,'>',$log_dir . "\/extract.log") or die $!;
print $lh "Extraction des CVs de la base MySQL Jems:\n\n";
while (@data = $sth->fetchrow_array()) {
print $lh "$data[0].$data[3]\n";
open(my $fh,'>',$extract_dir . "\/" . $data[0] . "." . $data[3]) or die $!;
binmode $fh;
print $fh $data[1];
close $fh;
}
close $lh;
$sth->finish;
$dbh->disconnect; |
Partager