3 # By Abel `00z' Camarillo
10 # Adds my own library dir.
16 #############################
17 # Global non-user variables #
18 #############################
21 my $helper = The00z::Helper->new
23 '<header>' => "$0 this program takes a CSV as first argument and generate"
24 . " a .parsed and .junk, it takes an `edificio' number as second argument "
25 . "and also updates the db with its registers.\r \r Options:",
26 '-h --help' => "Shows help and exit.",
27 '-u --db_user' => "Specify the db_user, if this option is not "
28 . "specified then the environment variable DB_NAME is read for fetch "
29 . 'its value, if it does not exist then the program exit with a db error.',
30 '-p --db_passwd' => "Specify the db_passwd, if this option is not "
31 . "specified then the environment variable DB_PASSWD is read for fetch "
32 . 'its value, if it does not exist then the program exit with a db error.',
33 '-n --db_name' => "Specify the db_name, if this option is not "
34 . "specified then the environment variable DB_NAME is read for fetch "
35 . 'its value, if it does not exist then the program exit with a db error.',
36 '-d --debug' => "Shows debug information, each time you use this "
37 . "option the verbosity level increase by one showing more information.",
38 '-l --logfile' => "Dump log to file."
41 ############################
42 # Configuration variables #
43 ############################
45 # extension for filtred (selected to pass) data
48 # extension for invalid ( format ) data
49 $INVALID = ".invalid";
53 push @csv_keys , "id";
54 push @csv_keys , "cuenta";
55 push @csv_keys , "origen";
56 push @csv_keys , "destino";
57 push @csv_keys , "d_context";
58 push @csv_keys , "cliente_id";
59 push @csv_keys , "canal";
60 push @csv_keys , "troncal";
61 push @csv_keys , "last_app";
62 push @csv_keys , "last_data";
63 push @csv_keys , "start";
64 push @csv_keys , "answer";
65 push @csv_keys , "end";
66 push @csv_keys , "duration";
67 push @csv_keys , "bill_sec";
68 push @csv_keys , "disposition";
69 push @csv_keys , "ama_flags";
72 # Convert seconds to hours:minutes:seconds
78 my $seconds = shift @_;
80 return unless $seconds =~ /^[[:digit:]]+$/;
82 if ( $seconds >= 3600 )
84 $hours = int $seconds / 3600 ;
85 $seconds = $seconds - $hours * 3600;
90 $minutes = int $seconds / 60;
91 $seconds = $seconds - $minutes * 60;
95 $seconds =~ s/^.*(.{2})$/$1/g;
98 $minutes =~ s/^.*(.{2})$/$1/g;
101 $hours =~ s/^.*(.{2})$/$1/g;
103 return join ':', $hours, $minutes, $seconds;
106 # returns an array wich elements are all the elements of all the rows
111 my ($dbh, $sql ) = @_;
112 $logger->write(2,qq/`$sql'\n/);
113 my $rows = $dbh->selectall_arrayref("$sql");
114 return ( @$rows ? @{@$rows[0]}[0] : undef);
117 # A *very* expensive operation, use with caution.
118 # Print ALL the tables of the db.
123 $logger->write(2,'-' x 50 . "\n");
124 $logger->write(2,"select * from llamada\n");
125 map { $logger->write(2,join (',', @$_). "\n"); } @{$dbh->selectall_arrayref('select * from
127 $logger->write(2,'-' x 50 . "\n");
128 $logger->write(2,"select * from extension\n");
129 map { $logger->write(2,join (',', @$_). "\n"); } @{$dbh->selectall_arrayref('select * from
131 $logger->write(2,'-' x 50 . "\n");
132 $logger->write(2,"select * from extension_llamada\n");
133 map { $logger->write(2,join (',', @$_). "\n"); } @{$dbh->selectall_arrayref('select * from
134 extension_llamada')};
135 $logger->write(2,'-' x 50 . "\n");
136 $logger->write(2,"select * from edificio_extension\n");
137 map { $logger->write(2,join (',', @$_). "\n"); } @{$dbh->selectall_arrayref('select * from
138 edificio_extension')};
139 $logger->write(2,'-' x 50 . "\n");
145 # Use the already open db connection
152 # Add the quotes needed by MySQL
153 map { $data->{$_} = $dbh->quote($data->{$_}) } keys %$data;
155 my ($results, $extension_id);
157 # FIXME: an extension should already be at the db.
158 # FIXME: adds an extension to the db only if it does not is there
159 unless ($results = db_search($dbh
160 , qq/select extension.id
161 from edificio, edificio_extension, extension
162 where numero=$data->{extension} and edificio.id=$data->{edificio}
163 and edificio.id<=>edificio_extension.edificio_id
164 and edificio_extension.extension_id<=>extension.id/) )
166 $sql = qq/INSERT INTO extension SET numero=$data->{extension} /;
167 $logger->write(2, "executing $sql\n");
169 $extension_id = db_search($dbh, qq/select last_insert_id()/)
171 $extension_id = $results;
174 $extension_id = $dbh->quote("$extension_id");
176 $sql = "INSERT INTO llamada SET
177 cuenta=$data->{cuenta}
178 , origen=$data->{origen}
179 , destino=$data->{destino}
180 , d_context=$data->{d_context}
181 , cliente_id=$data->{cliente_id}
182 , canal=$data->{canal}
183 , troncal=$data->{troncal}
184 , last_app=$data->{last_app}
185 , last_data=$data->{last_data}
186 , start=$data->{start}
187 , answer=$data->{answer}
189 , duration=$data->{duration}
190 , bill_sec=$data->{bill_sec}
191 , disposition=$data->{disposition}
192 , ama_flags=$data->{ama_flags}
198 $data->{'id'} = db_search($dbh, qq/select last_insert_id()/);
199 $data->{'id'} = $dbh->quote($data->{'id'});
201 # insert extension_llamada
202 unless ( $results = db_search($dbh, qq/select * from extension_llamada where
203 extension_llamada.extension_id<=>$extension_id
204 and extension_llamada.llamada_id=$data->{id}/))
206 $sql = qq/INSERT INTO extension_llamada SET
207 extension_id=$extension_id
208 , llamada_id=$data->{'id'}/;
210 $logger->write(2, "executing $sql\n");
214 # insert edificio_extension
215 unless ( $results = db_search($dbh
216 , qq/select extension_id from edificio_extension where
217 extension_id=$extension_id/))
219 $sql = qq/insert into edificio_extension set
220 edificio_id=$data->{edificio}
221 , extension_id=$extension_id/;
223 $logger->write(2, "executing $sql\n");
228 print_tables $dbh if $logger->level() > 1;
230 $logger->write(2, "\n", "x" x 50, "\n");
234 #############################
236 #############################
238 my ($loglevel, $logfile ) = (0, "/dev/stderr");
240 %db_data = ( 'db_user' => undef, 'db_passwd' => undef, 'db_name' => undef);
242 GetOptions ('help|h' => sub { $helper->show; exit 1;}
243 , 'debug|d+' => \$loglevel
244 , 'db_user|u=s' => \$db_data{'db_user'}
245 , 'db_passwd|p=s' => \$db_data{'db_passwd'}
246 , 'db_name|n=s' => \$db_data{'db_name'}
247 , 'logfile|l=s' => \$logfile
250 our $logger = The00z::Logger->new($logfile);
252 $logger->level($loglevel);
254 # Gets database access data
255 @db_data{sort keys %db_data}
256 = map { ( ! defined $db_data{$_} and $ENV{uc()} ) ? $ENV{uc()} : ''}
259 $logger->write(1, join ( ' : ',
260 map { "`$_' => " . ($db_data{$_} ? "`$db_data{$_}'" : 'undef')}
261 keys %db_data ), "\n") if $logger->level > 0;
263 map {croak "ERROR: I need a $_" unless $db_data{$_}} sort keys %db_data;
265 croak "ERROR: $0 : requiere dos argumentos para trabajar.\n"
266 . "$0 --help para ayuda."
269 $logger->write(2, "Entrando en modo debug (".$logger->level().")...\n");
271 # First argument name of file
272 chomp(my $current_filename = shift @ARGV);
274 # Second argument name of `edificio'
275 my $edificio = shift @ARGV;
277 print STDERR "Parsing : $current_filename\n";
278 print STDERR "Edificio id : $edificio\n";
280 (my $parsed_filename = $current_filename) =~ s/(\.csv)?$/$PARSED/i;
282 (my $invalid_filename = $current_filename ) =~ s/(\.csv)?$/$INVALID/i;
284 (my $tmp_filename = $current_filename ) =~ s/(\.csv)?$/.$$.tmp/i;
288 open CURRENT, "<$current_filename"
289 or croak "ERROR: can not open `$current_filename' ...$!";
290 open PARSED, ">$parsed_filename"
291 or croak "ERROR: can not open `$parsed_filename' ...$!";
292 open INVALID, ">$invalid_filename"
293 or croak "ERROR: can not open `$invalid_filename' ...$!";
295 # when this file is full it will replace $current_filename, it is used for the
296 # adition of ids to the `llamadas' when they are put into the db
297 open TMP , ">$tmp_filename"
298 or croak "ERROR: can not open `$tmp_filename'... $!";
300 # Open a DB connection
301 my $dbh = DBI->connect( "DBI:mysql:$db_data{db_name}"
302 ,"$db_data{db_user}" ,"$db_data{db_passwd}", {PrintError=>0,RaiseError=>1})
303 or croak "ERROR: $!";
305 # FIXME: an edificio should already be at the db.
306 # FIXME: adds an edificio to the db only if it does not is there
307 unless ( db_search($dbh
308 , qq/select id from edificio where id='$edificio'/))
310 my $sql = qq/INSERT INTO edificio SET id='$edificio'/;
311 $logger->write(2, "executing $sql\n");
317 my $invalid_lines = 0;
323 print STDERR "Parsed ... $lines lines.\r"
324 if ( scalar ($lines % 1000) == scalar 0);
328 # Remove those commas from hell
331 # the default id for a llamada equals to 0
332 # unless it already include an id.
333 my @line = split ',';
337 # the line is valid if it has the same number of elements of the template
340 # This prevents the insertion of the same csv twice
341 $valid = ( @line == @csv_keys) ? 1 : 0;
343 # if this line is valid then it already have an id therefore it is at the db
344 # then do not put it there.
353 # if this line if invalid let see if it is valid if we add a NULL (0) id.
354 unshift @line, ('0');
356 $valid = ( @line == @csv_keys) ? 1 : 0;
358 # if it is still invalid then send it to the invalid file without changes.
363 # prints a copy to the tmp file
365 print INVALID "$_\n";
369 # if not then parse it!!
372 # populate the hash of data
373 @data{@csv_keys} = @line;
375 # Extracts the extension from the cliente_id
376 ( $data{'extension'} )
377 = $data{'cliente_id'}
378 =~ m/^[^[:digit:]]*([[:digit:]]{4})[^[:digit:]]*.*$/;
380 $data{'extension'} = ''
381 unless ( $data{'extension'} and $data{'extension'} =~ /^[[:digit:]]{4}$/);
383 # Convert time from seconds to HOURS:MIN:SECONDS
384 $data{'duration'} = &convert_to_time($data{'duration'});
385 $data{'bill_sec'} = &convert_to_time($data{'bill_sec'});
387 ( $data{'edificio'} = $edificio ) =~ s/^0*//g;
389 # expensive debugging info
392 , (map { $data{$_} ? qq/$_ => `$data{$_}'\n/ : qq/$_ => undef\n/ }
394 if $logger->level() >= 1 ;
396 $logger->write(2,"|" x 50 . "\n", "$_\n", "|" x 50 . "\n");
398 # second argument number of `edificio'.
399 # Remove all trailing zeros.
400 put_into_db $dbh, \%data;
403 # stores the actual llamada id at the file text
411 rename "$current_filename", "$current_filename.bak"
412 or warn "ERROR: $!" unless -e "$current_filename.bak";
413 rename "$tmp_filename" , "$current_filename"
417 print STDERR "Parsed ... $lines lines.\r";
420 -----------------------------------------------------------------------------
421 @>>>>>>>> lineas analizadas de : ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
422 $lines, $current_filename
423 ~~^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
425 @>>>>>> lineas útiles enviadas a : ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
426 $good_lines, $parsed_filename
427 ~~^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
429 @>>>>>> lineas enviadas a la DB.
431 @>>>>>> lineas inválidas enviadas a : ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
432 $invalid_lines, $invalid_filename
433 ~~^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<