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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
|
#!C:\Perl64\bin\
use strict;
use warnings;
use DBI;
use Text::CSV;
use List::Util "first";
#Ouverture du fichier Client en Lecture
my $FicCli = "C:/Users/A454222/Documents/__Dev Perl/Test sort/FluxMailing_FL17_2.csv";
my $Client_Csv = Text::CSV->new ({
binary => 1, # Allow special character. Always set this
auto_diag => 1, # Report irregularities immediately
allow_loose_quotes => 1,
sep_char => ";"
});
open(Cli_Csv, "<", $FicCli) or die $!;
#Ouverture du fichier Agence en Lecture
my $FicAgence = "C:/Users/A454222/Documents/__Dev Perl/Test sort/File to sort/FluxGuichetPriPro_FL17.csv";
my $Agence_Csv = Text::CSV->new ({
binary => 1, # Allow special character. Always set this
allow_loose_quotes => 1,
auto_diag => 1, # Report irregularities immediately
sep_char => ";"
});
open(Age_Csv, "<", $FicAgence) or die $!;
#Ouverture du fichier Agence Trié en Ecriture
my $FicSorted = "C:/Users/A454222/Documents/__Dev Perl/Test sort/File_Sorted.csv";
open(FicSort, ">", $FicSorted) or die $!;
#Ouverture du fichier Result en Ecriture
my $FicResult = "C:/Users/A454222/Documents/__Dev Perl/Test sort/Result_Full.txt";
open(FicRes, ">", $FicResult) or die $!;
#Ouverture du fichier Erreur "Matching entre le fichier Mailing et le Fichier Agence impossible" en Ecriture
my $FicErrorCodeAgence = "C:/Users/A454222/Documents/__Dev Perl/Test sort/Erreur_Matching_Mailing_Agence.txt";
open(FicErrMatching, ">", $FicErrorCodeAgence) or die $!;
#Ouverture du fichier Erreur "Caractère Interdit Fichier Mailing" en Ecriture
my $FicErrorMailing = "C:/Users/A454222/Documents/__Dev Perl/Test sort/Erreur_Caractere_Interdit_Fichier_Mailing.txt";
open(FicErrMailing, ">", $FicErrorMailing) or die $!;
#Ouverture du fichier Erreur "Caractère Interdit Fichier Agence" en Ecriture
my $FicErrorAgence = "C:/Users/A454222/Documents/__Dev Perl/Test sort/Erreur_Caractere_Interdit_Fichier_Agence.txt";
open(STDOUT, ">", $FicErrorAgence) or die $!;
##########################################################
################# Debut de la Partie Tri #################
##########################################################
#Creation de la DataBase pour le Tri du fichier Agence
my $dbh = DBI->connect('dbi:CSV:', undef, undef, {
RaiseError => 1,
f_ext => '.csv',
f_dir => "File to sort",
f_dir_search => "File sorted",
csv_sep_char => ";",
csv_tables => { FluxGuichetPriPro_FL17 => { col_names => [qw' Code_DEC Code_Agence Code_DR Nom_Agence Type_Agence Cplt_Intitule_Adresse Rue Lieu_Dit Code_Postal Ville Num_Tel_Signataire Civilite Prenom Nom '] } },
});
#Creation de la requete SQL pour le Tri sur le Champ Code_Agence
my $sth = $dbh->prepare(q{
SELECT * FROM FluxGuichetPriPro_FL17 ORDER BY Code_Agence
});
#Execution de la requête
$sth->execute;
my %Hash;
my $CodeAgenceCli = 0;
my $count = 0;
#Ecriture du Tableau trié dans le fichier File_Sorted.csv
while ( my @row = $sth->fetchrow_array ) {
my $LineSorted = join(';' => @row);
print(FicSort $LineSorted,"\n");
$CodeAgenceCli = $row[1] ;
#%Hash = ($CodeAgenceCli => $LineSorted);
${$Hash{$CodeAgenceCli}}[0] = $row[1]; #Code_Agence
${$Hash{$CodeAgenceCli}}[1] = $row[3]; #Nom_Agence
${$Hash{$CodeAgenceCli}}[2] = $row[5]; #Cplt_Intitule_Adresse
${$Hash{$CodeAgenceCli}}[3] = $row[6]; #Rue
${$Hash{$CodeAgenceCli}}[4] = $row[7]; #Lieu_Dit
${$Hash{$CodeAgenceCli}}[5] = $row[8]; #Code_Postal
${$Hash{$CodeAgenceCli}}[6] = $row[9]; #Ville
${$Hash{$CodeAgenceCli}}[7] = $row[10]; #Num_Tel_Signataire
}
close FicSort;
$sth->finish;
$dbh->disconnect;
my $NumTel;
my $NumTelFormateFull;
while (<Cli_Csv> ) {
next if ($. == 1);
if ($Client_Csv->parse($_)) {
my @ColCliMailing = $Client_Csv->fields();
$CodeAgenceCli = $ColCliMailing[2]; #Recupere le Code_Agence de la Ligne Courante
$count++;
$NumTel = ${$Hash{$CodeAgenceCli}}[7];
$NumTelFormateFull = join (" ","TEL. :",join (".",substr($NumTel,-10,2),substr($NumTel,-8,2),substr($NumTel,-6,2),substr($NumTel,-4,2),substr($NumTel,-2,2)));
#print (FicRes CodeBanque;CodeSG3N;CodePostal;FR/ETR;Code Guichet;Numéro de compte;Annexe1;Annexe2;Annexe3;Annexe4;Id adresse (gestion PND);DestLignAdr1;DestLignAdr2;DestLignAdr3;DestLignAdr4;DestLignAdr5;DestLignAdr6;Nom_Agence;Cplt_Intitule_Adresse;Rue;Lieu_Dit;Code_Postal Ville;Num_Tel_Signataire;Var1;Var2;Var3;Var4;Var5;Var6;Var7;Var8;Var9;Var10;)
print (FicRes "30003",";",";",$ColCliMailing[19],";","FR",";",$ColCliMailing[2],";",sprintf("%016u",$count),";"," ",";"," ",";"," ",";"," ",";"," ",";",$ColCliMailing[14],";",$ColCliMailing[15],";",$ColCliMailing[16],";",$ColCliMailing[17],";",$ColCliMailing[18],";",$ColCliMailing[19]," ",$ColCliMailing[20],";",${$Hash{$CodeAgenceCli}}[1],";",${$Hash{$CodeAgenceCli}}[2],";",${$Hash{$CodeAgenceCli}}[3],";",${$Hash{$CodeAgenceCli}}[4],";",${$Hash{$CodeAgenceCli}}[5]," ",${$Hash{$CodeAgenceCli}}[6],";",$NumTelFormateFull,";",${$Hash{$CodeAgenceCli}}[0],";",$ColCliMailing[7],";",$ColCliMailing[8],";",";",";",";",";",";",";","\n");
}
} |
Partager