· 8 years ago · Mar 08, 2018, 03:54 AM
1#!/usr/bin/perl -w
2
3##############################################################################
4# Handles log file raw import - Step 1 of importing the data.
5#
6# Reads out the logfile and writes the data into the 'data' table.
7#
8# Continues with 'searchdir.pl' when finished.
9#
10# rleuthold@access.ch - 8.1.2009
11##############################################################################
12
13use strict;
14use DBI;
15use POSIX;
16use Fcntl;
17use Data::Dumper;
18use Date::Calc qw(:all);
19use File::stat;
20use IO::File;
21
22use lib 'lib';
23use lib::DBHandler;
24use lib::XMLPaths;
25use lib::DBTables;
26
27# VARIABLES
28my @ARGS;
29my @STARTTIME;
30my @ENDTIME;
31my $RESULTFILE;
32
33# counters
34my $DATACOUNT = 0;
35
36##############################################################################
37
38##
39# Global variables
40my $DBH;
41my $TABLES = DBTables->new();
42my $PATHS = XMLPaths->new();
43my $PERLCONFIG = PerlConfig->new();
44
45# Paths / directories
46my $DATA_PATH = $PATHS->get_path('data');
47my $IMPORTED_FOLDER = $PATHS->get_path('imported');
48my $IMPORTED_LOGS_FOLDER = $PATHS->get_path('importedlogs');
49
50my $SCRIPT_PATH = $PERLCONFIG->get_scriptsfolder();
51my $DB = DBHandler->new->{DB};
52
53# database tables
54my $TABLE_DATA = $TABLES->get_table_name('data');
55my $TABLE_LOGS = $TABLES->get_table_name('logfiles');
56my $TABLE_RFIDS = $TABLES->get_table_name('rfids');
57
58my $DAYS_TO_COUNT_TABLE = $TABLES->get_days_to_count_table();
59
60my $BACKUPUSER = $PERLCONFIG->get_dbbackupuser();
61my $DBBACKUPDIR = $PERLCONFIG->get_dbbackupdirectory();
62
63my $DATEFORMAT = "20%02d-%02d-%02d %02d:%02d:%02d";
64my $DAY_FOLDER;
65
66####################################
67# PREAMBLE
68####################################
69
70print"\n================================================\n";
71print"STARTING LOGIMPORT.PL";
72print"\n================================================\n";
73
74
75my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;
76$year += 1900;
77$mon++;
78
79###############################################
80# Creating needed folders
81
82umask(000); # UNIX file permission junk
83(mkdir($IMPORTED_FOLDER, 0771) || die ("Could not create folder $IMPORTED_FOLDER: $!")) unless (-d $IMPORTED_FOLDER);
84
85my $dayDate = $mday."_". $mon ."_". $year ."/";
86$DAY_FOLDER = $IMPORTED_FOLDER . $dayDate;
87(mkdir($DAY_FOLDER, 0771) || die ("Could not create folder $DAY_FOLDER: $!")) unless (-d $DAY_FOLDER);
88
89(mkdir($IMPORTED_LOGS_FOLDER, 0771) || die ("Could not create folder $IMPORTED_LOGS_FOLDER: $!")) unless (-d $IMPORTED_LOGS_FOLDER);
90
91##
92# setting up file for output
93my $filename = $DAY_FOLDER . "logimport_log\.txt";
94
95##
96# opening file for writing
97sysopen (RES, $filename, O_CREAT |O_WRONLY, 0755) or die("Can't open file $filename : $!");
98
99#############################################################################
100# open db connection
101$DBH = DBHandler->new()->connect();
102#############################################################################
103
104###############################################
105# Create 'temporary' table to store the days which have to be counted in the counter.pl script.
106$DBH->do(qq{DROP TABLE IF EXISTS $DAYS_TO_COUNT_TABLE}) || die ("Could not drop table '$DAYS_TO_COUNT_TABLE': " . $DBH->errstr);
107$DBH->do(qq{CREATE TABLE `$DAYS_TO_COUNT_TABLE` (`day` date NOT NULL, PRIMARY KEY (`day`) )}) || die ("Could not create table '$DAYS_TO_COUNT_TABLE': " . $DBH->errstr);
108
109##
110# get logfiles
111my @logFiles = map {/(\d{8}_\d{6}\.txt)/} <$DATA_PATH* .txt>;
112
113##
114# getting the time info (yy-mmddhhmnss - readable by the Datemanip module) out of the filename
115my %logFiles;
116foreach (@logFiles) {
117 my $log = $_;
118 my ($y, $m, $d, $h, $min, $sec) = /\d{2}(\d{2})(\d{2})(\d{2})_(\d{2})(\d{2})(\d{2})\.txt/;
119
120 $logFiles{$y."-".$m.$d.$h.$min.$sec} = $log;
121}
122
123print "======================================================\n";
124printf RES "======================================================\n";
125
126if( keys %logFiles == 0) {
127 print"No Files to import for today ... bye\n";
128 printf RES"No Files to import for today ... bye\n";
129 exit;
130} else {
131 print "Files to import:\n";
132 printf RES "Files to import:\n";
133
134 my $i = 1;
135 for my $file( sort keys %logFiles ) {
136 print "\t$i.)\t$file => $logFiles{$file}\n";
137 printf RES "\t$i.)\t$file => $logFiles{$file}\n";
138 $i++;
139 }
140
141 # db backup backup
142 ( mkdir($DBBACKUPDIR, 0771) || die("Cannot create database backup directory: $!") ) unless (-d $DBBACKUPDIR); # create backup dir if necessary
143 my $dbbackupfile = $DBBACKUPDIR . 'micedata_'. $year . '_' . $mon . '_' . $mday . '.sql.bz2';
144
145 print "\n======================================================\n";
146 printf RES "\n======================================================\n";
147
148 if (-e $dbbackupfile) {
149 print "\nBackup file '$dbbackupfile' for today already exists, skipping backup ...\n";
150 printf RES "\nBackup file '$dbbackupfile' for today already exists, skipping backup ...\n";
151 } else {
152 print "\nBacking up database '$DB' to file '$dbbackupfile' ...\n";
153 printf RES "\nBacking up database '$DB' to file '$dbbackupfile' ...\n";
154
155 my @ARGS = ("/usr/bin/mysqldump --opt -u $BACKUPUSER --lock-tables $DB | /bin/bzip2 -c > $dbbackupfile");
156 system(@ARGS) == 0
157 or die "Backup process '@ARGS' failed: $?";
158
159 print "... backup complete.\n";
160 printf RES "... backup complete.\n";
161
162 }
163
164 print "------------------------------------------------------\n";
165 printf RES "------------------------------------------------------\n";
166}
167
168##############################################################
169# MAIN
170##############################################################
171#
172## Now that we have the log time in the key and the LOGFILE name in the value let's start
173for my $logFile( sort keys %logFiles ) {
174
175
176 #print $STARTTIME ."\n";
177 my $fileName = $logFiles{$logFile};
178
179 next if (&CheckImport($fileName) == 1); # check if the file has already been imported and skip if it is so
180
181
182
183 ###################################
184 # send each file to the main loop
185 ###################################
186 &File($logFile,$fileName); # send each file in the main loop
187
188 ##
189 # updating logs table
190 my $size = ((stat($DATA_PATH.$fileName)->size)/1024);
191
192 my $logSQL = qq {INSERT INTO $TABLE_LOGS(logfile, short, start, end, size, duration,import)
193 VALUES(
194 '$fileName',
195 '$logFile',
196 (SELECT MIN(time) FROM $TABLE_DATA WHERE import='$logFile' ),
197 (SELECT MAX(time) FROM $TABLE_DATA WHERE import='$logFile' ),
198 '$size',
199 (SELECT TIMEDIFF((select max(time) from data where import='$logFile'),(select min(time) from data where import='$logFile'))),
200 NOW())
201 };
202
203
204 $DBH->do($logSQL)
205 or die("Could not update or insert value: " . $DBH->errstr );
206
207
208 ##
209 # Get the days for this log file has data for and insert them into the table with the days to count
210 $DBH->do(qq{INSERT IGNORE INTO $DAYS_TO_COUNT_TABLE (`day`) SELECT DISTINCT DATE(`time`) FROM $TABLE_DATA WHERE import='$logFile'})
211 or die("Could not execute statement to insert day into $DAYS_TO_COUNT_TABLE for $logFile: " . $DBH->errstr);
212
213 # move analyzed file
214 @ARGS = ("/bin/mv", "$DATA_PATH$fileName", "$IMPORTED_LOGS_FOLDER$fileName");
215 system(@ARGS) == 0
216 or die "Moving file failed from $DATA_PATH$fileName to $IMPORTED_LOGS_FOLDER$fileName: $?";
217
218 ##
219 # uncomment to test one file
220 #exit;
221
222
223}
224
225
226
227
228#########################################################
229# ENDING
230#########################################################
231
232# close db and result File
233$DBH->disconnect();
234close (RES);
235
236print"\n================================================\n";
237print" LOGIMPORT.PL COMPLETE";
238print"\n================================================\n";
239
240
241# continue with analyzing data for direction pairs
242my @args = ( "/usr/bin/perl -I$SCRIPT_PATH " . $SCRIPT_PATH."searchdir.pl");
243system(@args) == 0
244 or die "system @args failed: $?";
245exit;
246
247
248
249#########################################################
250# SUBS
251#########################################################
252
253##
254# kind of main loop for one file
255 sub File {
256
257 my ($logFile, $fileName) = @_;
258
259 #################################
260 # open LOGFILE for read and clean it from silly windows style line feeds (^M)
261 #
262 #print "reading file: $logFile\n";
263 open(LOG, "< $DATA_PATH$fileName") or die("Can't open file $DATA_PATH$fileName: $!");
264 open(LOG_CLEAN, "+> $DATA_PATH$fileName.tmp") or die("Can't open file $DATA_PATH$fileName.tmp: $!");
265
266
267 while (my $line = <LOG>) {
268
269 $line =~ s/^\s+//g;
270
271 if($line =~ m/\x0D/) {
272 chomp($line);
273 $line =~ s/\x0D/\n/g;
274 }
275
276 print LOG_CLEAN $line;
277 }
278
279 close(LOG_CLEAN);
280 close(LOG);
281
282 open(LOG_CLEAN, "< $DATA_PATH$fileName.tmp") or die("Can't open file $DATA_PATH$fileName.tmp: $!");
283
284
285 rename("$DATA_PATH$fileName.tmp", "$DATA_PATH$fileName");
286 open(LOG, "< $DATA_PATH$fileName") or die("Can't open file $DATA_PATH$fileName: $!");
287 #################################
288
289 # reading in LOGFILE and put the data into a table named after the LOGFILE time info
290
291 ##
292 # preparing mysql insert
293 my $sth = $DBH->prepare("INSERT INTO `$TABLE_DATA` (time, millisec, ant, rfid, import) VALUES(?,?,?,?,?)")
294 or die("Could not prepare insert statement: " . $DBH->errstr);
295 my $sthRfid = $DBH->prepare("INSERT INTO $TABLE_RFIDS (id,i) VALUES(?, 0)")
296 or die("Could not prepare update statement: " . $DBH->errstr);
297
298 ##
299 # when the file is exported from excel it has ^M as the newline character ... replace it with a normal newline
300
301 my $line_count = 0;
302 my $day = '';
303 while (my $lineLog = <LOG>) {
304
305 $line_count++;
306
307 next if($lineLog =~ m/\t0\t?$/); # get rid of the size 0 nonsense data (log files with tabs as delimiter)
308 next if($lineLog =~ m/\s+0;\s*$/); # get rid of the size 0 nonsense data (log files with spaces as delimiter)
309
310 # inform user
311 (($DATACOUNT % 100) == 0) ? print "\n[$DATACOUNT] lines read\t" : print ".";
312 (($DATACOUNT % 100) == 0) ? printf RES "\n[$DATACOUNT] lines read\t" : printf RES ".";
313
314 ##
315 # prepare the data
316 my ($datetime, $millisec, $ant, $rfid) = LineManip($lineLog);
317 if(defined $datetime) {
318 my @records = ($datetime, $millisec, $ant, $rfid, $logFile);
319
320 ##
321 # insert data into data table
322 $sth->execute(@records)
323 or die("Could not insert into $TABLE_DATA: " . $DBH->errstr );
324
325 ##
326 # update rfid in rfids table
327 $sthRfid->execute($records[3])
328 or die("Could not update rfid table: " . $DBH->errstr );
329 } else {
330 print"\n[$line_count] WARNING: Skipping the line. Maybe malformed: $lineLog\n";
331 printf RES "\n[$line_count] WARNING: Skipping the line. Maybe malformed: $lineLog\n";
332
333 }
334
335
336 $DATACOUNT++;
337
338 }
339
340 close(LOG);
341 print"\nread: [$fileName]\t data sets: $DATACOUNT\n";
342 printf RES "\n====================\n";
343 printf RES "Data sets:\t$DATACOUNT\n";
344 printf RES "======================\n";
345 $DATACOUNT = 0;
346
347 }
348
349##
350# Checks if this file has already been imported
351sub CheckImport {
352
353 my $fileName = shift;
354
355 my $checkSQL = qq{SELECT count(logfile) FROM $TABLE_LOGS WHERE logfile='$fileName'};
356 my $check = $DBH->selectrow_array($checkSQL,undef);
357
358 if (($DBH->selectrow_array($checkSQL,undef)) == 1) { # remove the file from the data directory if it
359 @ARGS = ("rm", $DATA_PATH.$fileName); # has already been imported
360 system(@ARGS) == 0 or die "system @ARGS failed: $?";
361 print"\nERROR: attempt to reimport [$fileName ]. The file has been deleted from $DATA_PATH\n";
362 return 1;
363 } else {
364 return 0;
365 }
366
367 return ();
368
369}
370
371##
372# Manipulate the data line to get the data we need and format it so that we can put it in the database table
373sub LineManip{
374
375 my $logLine = shift;
376
377 # get values
378 $_ = $logLine;
379
380 #
381 # some regex to get the values
382 my ($datetime,$millisec,$deli1, $ant, $deli2, $deli3, $rfid) =
383 /.*(\d{4}\-\d{2}\-\d{2}\s+\d{2}:\d{2}:\d{2}):(\d{3});?(\s+|\t)(\S{2,3});?(\s+|\t)\d;?(\s+|\t)(\w{2}\s\w{2}\s\w{2}\s\w{2}\s\w{2}).*/;
384
385 ##
386 # check if values are defined
387 my @check_values = ($datetime,$millisec,$deli1, $ant, $deli2, $deli3, $rfid);
388 my $check = 1;
389
390 foreach my $value(@check_values) {
391 if(!defined $value) {
392 $check = 0;
393 last;
394 }
395 }
396
397 if($check == 0) {
398 return(undef, undef, undef, undef);
399 } else {
400
401 ##
402 # get rid of whitespaces in the rfid
403 $rfid =~ s/\s//g;
404
405 ##
406 # Some special handling for the badly adressed antennas
407 $ant =~ s/1A/16/;
408 $ant =~ s/2A/17/;
409
410 ##
411 # Antennas 421 and 423 map to box 13 (Mail from B.Koenig 30.12.2008)
412 $ant =~ s/421/131/;
413 $ant =~ s/423/133/;
414
415 ##
416 # fromatting antenna
417 $ant = sprintf('%03s', $ant);
418
419 ##
420 # fromatting millisec
421 $millisec = sprintf('%03s', $millisec);
422
423 return($datetime, $millisec, $ant, $rfid);
424 }
425
426}