· 8 years ago · Mar 08, 2018, 03:54 AM
1#!/usr/bin/perl -w
2
3##############################################################################
4# Searches for results - Step 3 of importing the data.
5#
6# Continues with 'counter.pl' when finished.
7#
8# rleuthold@access.ch - 8.1.2009
9##############################################################################
10
11use strict;
12use DBI;
13use Cwd;
14use Fcntl;
15use Date::Calc qw(Delta_YMDHMS);
16use DateTime::Format::Strptime;
17use Data::Dumper;
18
19use lib 'lib';
20use lib::DBHandler;
21use lib::XMLPaths;
22use lib::DBTables;
23
24##
25# Global variables
26my $DBH;
27my $TABLES = DBTables->new();
28my $PATHS = XMLPaths->new();
29my $PERLCONFIG = PerlConfig->new();
30
31# Paths / directories
32my $DATA_PATH = $PATHS->get_path('data');
33my $SUBFOLDER = $PATHS->get_path('imported');
34
35my $SCRIPT_PATH = $PERLCONFIG->get_scriptsfolder();
36
37# database tables
38my $TABLE_DATA = $TABLES->get_table_name('data');
39my $TABLE_RFIDS = $TABLES->get_table_name('rfids');
40my $TABLE_DIR = $TABLES->get_table_name('direction_results');
41my $TABLE_RES = $TABLES->get_table_name('results');
42
43my $DAYS_TO_COUNT_TABLE = $TABLES->get_days_to_count_table();
44
45my $RFIDDIRINDATA = ();
46my @RFIDDIRINDATASK = ();
47my $RFIDDIROUTDATA = ();
48my @RFIDDIROUTDATASK = ();
49my $RFIDOUTDATA = ();
50my @RFIDOUTDATASK = ();
51my @RFIDRESSORTEDSK = ();
52
53# some counters
54my $DATACOUNT = 0; # number of (in) datasets
55my $RESCOUNT = 0; # number if results
56my $DIRRESCOUNT = 0; # number of direction result
57my $DATARESCOUNT = 0; # number of data results
58my $NORESCOUNT = 0; # number of in datasets for which no resuult could be found
59my $OVERLAPS = 0; # number of overlaps
60my $RFIDCOUNT = 0; # number of rfids
61
62#my $BOX_OUT; # store the time from which we continue to search
63my $PRINTF = "%-12s%-7s%-22s%-22s%-9s\n";
64my $PRINTFSEP = "-----------+------+---------------------+---------------------+--------+\n";
65
66
67####################################
68# PREAMBLE
69####################################
70print"\n================================================\n";
71print "STARTING SEARCHRES.PL\n\n";
72print "Symbols:\n";
73print "\t+ => direction result (1-3) -> (3-1)\n";
74print "\t& => data result (1-3) -> (1)\n";
75print "\t- => no result\n";
76print "\to => overlap (no result)\n";
77print"\n================================================\n";
78
79################################
80# set up results file
81
82##
83# where should we write the output
84
85my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;
86my $dayDate = $mday."_". ($mon+1) ."_". ($year+1900) ."/";
87my $DAY_FOLDER = $SUBFOLDER . $dayDate;
88
89##
90# Try to create folders if they don't exist
91mkdir($SUBFOLDER, 0771) unless (-d $SUBFOLDER);
92mkdir($DAY_FOLDER, 0771) unless (-d $DAY_FOLDER);
93
94my $filename = $DAY_FOLDER. "res_data\.txt"; # the filename with some kind of timestamp
95sysopen (RES, $filename, O_CREAT |O_WRONLY, 0755) or die("Can't open result file : $!");
96
97
98####################################
99# open db connection
100$DBH = DBHandler->new()->connect();
101
102######
103# specifying/preparing the sql statements which are used many times (for performance reasons)
104
105#my $LASTOUTRFID = $DBH->prepare(qq{SELECT MAX(box_out) FROM `$TABLE_RES` WHEERE rfid = ?})
106# or die("Could not prepare statement to last box out: " . $DBH->errstr);
107
108my $RFIDDIRDATAINSTH = $DBH->prepare(qq{SELECT id, rfid, time, UNIX_TIMESTAMP(time) AS unix_time, box, dir, outerdataid, innerdataid FROM `$TABLE_DIR` WHERE rfid= ? AND i=0 AND dir='in' AND `time` > ?})
109 or die( "Could not prepare statement to get rfid dir results: " . $DBH->errstr);
110
111my $RFIDDIRDATAOUTSTH = $DBH->prepare(qq{SELECT id, time, UNIX_TIMESTAMP(time) AS unix_time, box, dir, outerdataid, innerdataid FROM `$TABLE_DIR` WHERE rfid= ? AND i=0 AND dir = 'out' AND i=0 AND time > ?})
112 or die( "Could not prepare statement to get rfid dir results: " . $DBH->errstr);
113
114my $RFIDOUTDATASTH = $DBH->prepare(qq{SELECT id, time, UNIX_TIMESTAMP(time) AS unix_time, LEFT(ant,2) AS box, id AS outerdataid, id AS innerdataid FROM `$TABLE_DATA` WHERE rfid= ? AND i=1 AND RIGHT(ant,1) = '1' AND time > ?} )
115 or die("Could not prepare statement to get rfid data results: " . $DBH->errstr);
116
117my $INSRESSTH = $DBH->prepare(qq{INSERT INTO `$TABLE_RES` (rfid, box, box_in, box_out, dt, inid, outid, i, nerv_index) VALUES( ?, ?, ?, ?, SEC_TO_TIME(?), ?, ?, ?, ?)})
118 or die("Could not prepare statement to insert result: " . $DBH->errstr);
119
120my $UPDATEDATATABLE = $DBH->prepare(qq{UPDATE $TABLE_DATA SET i= ? WHERE id IN(?,?,?,?)})
121 or die("Could not prepare statement to update $TABLE_DATA: " . $DBH->errstr);
122
123my $UPDATEDIRTABLE = $DBH->prepare(qq{UPDATE $TABLE_DIR SET i= ? WHERE id IN(?,?)})
124 or die("Could not prepare statement to update $TABLE_DIR: " . $DBH->errstr);
125
126my $READINGS_DURING_DIRECTION_RESULT = $DBH->prepare(qq{SELECT * FROM $TABLE_DATA WHERE id != ? AND rfid=? AND time > ? AND time <
127 (SELECT time FROM $TABLE_DATA WHERE id = (SELECT innerdataid FROM $TABLE_DIR WHERE id = ?));
128})
129 or die("Could not prepare statement to check for other readings in table $TABLE_DATA: " . $DBH->errstr);
130
131my $READINGS_DURING_DATA_RESULT = $DBH->prepare(qq{SELECT * FROM $TABLE_DATA WHERE rfid=? AND time > ? AND time < ?})
132 or die("Could not prepare statement to check for other readings in table $TABLE_DATA: " . $DBH->errstr);
133
134my $UPDATEDATARESIDS = $DBH->prepare(qq{UPDATE $TABLE_DATA SET res_id= ? WHERE id IN(?,?,?,?)})
135 or die("Could not prepare statement to update $TABLE_DATA: " . $DBH->errstr);
136
137my $UPDATEDIRRESIDS = $DBH->prepare(qq{UPDATE $TABLE_DIR SET res_id= ? WHERE id IN(?,?)})
138 or die("Could not prepare statement to update $TABLE_DIR: " . $DBH->errstr);
139
140
141##############################################################
142# MAIN
143##############################################################
144
145##
146#Create 'temporary' table to store the days which have to be counted in the counter.pl script.
147$DBH->do(qq{CREATE TABLE IF NOT EXISTS `$DAYS_TO_COUNT_TABLE` (`day` date NOT NULL, PRIMARY KEY (`day`) )})
148 || die ("Could not create table '$DAYS_TO_COUNT_TABLE': " . $DBH->errstr);
149$DBH->do(qq{INSERT IGNORE INTO $DAYS_TO_COUNT_TABLE (`day`) SELECT DISTINCT DATE(`time`) FROM $TABLE_DIR WHERE i='0'})
150 || die ("Could not insert days to count into $DAYS_TO_COUNT_TABLE: " . $DBH->errstr);
151
152##
153# getting rfids which are already analyzed for direction
154my @RFIDS = map {$_->[0]} @{$DBH->selectall_arrayref("SELECT id FROM $TABLE_RFIDS WHERE i='2'")};
155
156#
157
158# print header text to the result file
159printf RES ( $PRINTF,"rfid","box","box_in","box_out","dt");
160printf RES $PRINTFSEP;
161
162# Now search for each rfid
163foreach my $rfid (@RFIDS) {
164
165 $RFIDCOUNT++;
166 print "[rfid $RFIDCOUNT of " . scalar(@RFIDS). "] => [$rfid]\t";
167
168 ##
169 # get the latest box out from the result table to avoid result overlap
170 my $max_time = shift(@{$DBH->selectcol_arrayref(qq{SELECT MAX(box_out) FROM `$TABLE_RES` WHERE rfid = '$rfid'})}) || '0000-00-00 00:00:00';
171
172 ##
173 # Get an array reference with the direction IN data for the rfid, the key is the time in the unix timestamp format
174 $RFIDDIRINDATA = SelArrayRef($RFIDDIRDATAINSTH, [qw{id rfid time unix_time box dir outerdataid innerdataid}], [$rfid, $max_time]);
175# $RFIDDIRDATAINSTH->execute($rfid,$max_time);
176# $RFIDDIRINDATA = $RFIDDIRDATAINSTH->fetchall_hashref('id');
177 ##
178 # Jump to next rfid if we have no IN data to search.
179 if(scalar keys(%$RFIDDIRINDATA) == 0) {
180 print "\n\t(no data)\n";
181 ##
182 # update the rfids table to indicate that this rfid has been searched for in/out pairs
183 $DBH->do(qq{UPDATE `$TABLE_RFIDS` set i='3', res=NOW() WHERE id = '$rfid'})
184 or die("Could not update $TABLE_RFIDS -> $rfid: " . $DBH->errstr);
185 next;
186 }
187
188 ##
189 # Sorted keys for the in data
190 @RFIDDIRINDATASK = @{&sortedHashKeysArray($RFIDDIRINDATA, 'unix_time')};
191 $DATACOUNT += scalar(@RFIDDIRINDATASK);
192
193 #
194 # Get an array reference with the direction OUT data for the rfid, the key is the time in the unix timestamp format
195 $RFIDDIROUTDATA = SelArrayRef($RFIDDIRDATAOUTSTH, [qw{id time unix_time box dir outerdataid innerdataid}], [$rfid, $max_time]);
196
197 ##
198 # Sorted keys for the OUT data
199 @RFIDDIROUTDATASK = @{&sortedHashKeysArray($RFIDDIROUTDATA, 'unix_time')};
200
201 ##
202 # Get an array reference with the OUT data for the rfid, the key is the time in the unix timestamp format
203 $RFIDOUTDATA = SelArrayRef($RFIDOUTDATASTH, [qw{id time unix_time box outerdataid innerdataid}], [$rfid, $max_time]);
204
205 ##
206 # Sorted keys for the data data
207 @RFIDOUTDATASK = @{&sortedHashKeysArray($RFIDOUTDATA, 'unix_time')};
208
209 ##
210 # main loop
211 my $rfidresults = {};
212 my $rfid_no_results = {};
213 @RFIDRESSORTEDSK = ();
214
215 ##
216 # Search for result pair
217 my $check_id_count = 0;
218 print "\n\t=> Getting results:\n\t";
219 my $rfid_data_count = scalar(@RFIDDIRINDATASK);
220
221 ##
222 # Looping through the IN direction data for the rfid and search for matching OUT data.
223 while (scalar(@RFIDDIRINDATASK) > 0 ) {
224
225 ##
226 # Getting matching OUT result for the IN result defined by the $toCheckId.
227 my $toCheckId = shift(@RFIDDIRINDATASK);
228
229 my $result = &SearchMatchOut($rfid, $RFIDDIRINDATA->{$toCheckId});
230
231 ##
232 # SearchMatchOut returns a hash reference with an 'in' and an out 'key' with the respective values.
233 # If we have no 'out' key, no matching out result could be found.
234 if( defined $result->{'out'} ) {
235
236 $result->{'result'} = 1;
237 $rfidresults->{$toCheckId} = $result; # add the result to the hash with the results for this rfid
238
239 ##
240 # Delete `used` direction 'out' key from the array with the sorted keys for the result type.
241 if($result->{'typ'} == 3) { # direction result
242 @RFIDDIROUTDATASK = @{&DelItemFromArray($result->{'out'}->{'index'}, \@RFIDDIROUTDATASK)};
243 } else { # data result
244 @RFIDOUTDATASK = @{&DelItemFromArray($result->{'out'}->{'index'},\@RFIDOUTDATASK)};
245 }
246
247 push(@RFIDRESSORTEDSK, $toCheckId);
248
249 ##
250 # No matching 'out' data could be found for this id
251 } else {
252 $result->{'result'} = 0;
253 $rfidresults->{$toCheckId} = $result;
254 }
255
256
257 $check_id_count++;
258 (($check_id_count % 100) == 0) ? print ". [$check_id_count]\n\t" : print ".";
259
260 ##
261 # uncomment next line to check only one id.
262 #exit;
263 }
264
265 print " [$rfid_data_count]";
266
267 ##
268 # After finding the result pairs we check for overlapping (time) results
269
270 ##
271 # Loop through results and check for overlaps.
272 $check_id_count = 0;
273 print "\n\t=> Solving overlap conflicts (+ = result overlap, * = dataset overlap, & = nervous, - = no conflict):\n\t";
274
275 while (scalar(@RFIDRESSORTEDSK) > 0) {
276
277 my $res_sorted_key = shift(@RFIDRESSORTEDSK);
278 my $res_sorted_key_overlap = searchforOverlap($res_sorted_key, $rfidresults);
279
280 $check_id_count++;
281
282 ##
283 # resolve the overlap conflict (mark the overlaps)
284 if ( scalar (@$res_sorted_key_overlap) > 0 ) {
285 (($check_id_count % 100) == 0) ? print "+ [$check_id_count]\n\t" : print "+";
286
287 ##
288 # case 1: The result in question overlaps many others.
289 #
290 # The chance of one `wrong` result (missed antenna reading) is
291 # higher the chance of multiple `false` readings building results together.
292 # So we mark the result (the one in question) which overlaps the other ones.
293 if( scalar(@$res_sorted_key_overlap) > 1) {
294 $rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
295
296 ##
297 # case 2: The result in question overlaps just one other.
298 #
299 # We skip both results (mark them as overlap).
300 #
301 } else {
302 $rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
303 $rfidresults->{(@{$res_sorted_key_overlap}[0])}->{'overlap'} = 1;
304 }
305
306 ##
307 # No result overlapping detectected
308 } else {
309
310 ##
311 # Check for DATASET overlapping
312 $rfidresults->{ $res_sorted_key }->{'overlap'} = 0;
313 $rfidresults->{ $res_sorted_key }->{'nerv_index'} = 0;
314 my $dataset_overlaps = &searchOtherReadings($res_sorted_key, $rfidresults);
315
316
317 # if we have dataset overlapping we check if the overlapped datasets are readings at the inner antenna (3)
318 # of the box this result takes place.
319 # If this is the case, this is a 'nervous result', because the mouse is checking the entry tube quite often.
320 # The nerv_index value specifies the number of readings at tthe inner antenna
321 # (some kind of measurement of how nervous the mouse is)
322 if(keys %$dataset_overlaps) {
323
324 if ( &checkNervousness($res_sorted_key, $rfidresults, $dataset_overlaps) ) {
325 (($check_id_count % 100) == 0) ? print "& [$check_id_count]\n\t" : print "&";
326 $rfidresults->{ $res_sorted_key }->{'nerv_index'} = scalar(keys %$dataset_overlaps);
327 } else {
328 (($check_id_count % 100) == 0) ? print "* [$check_id_count]\n\t" : print "*";
329 $rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
330 }
331
332 # no dataset overlapping
333 } else {
334 (($check_id_count % 100) == 0) ? print "- [$check_id_count]\n\t" : print "-";
335 $rfidresults->{ $res_sorted_key }->{'overlap'} = 0;
336 }
337
338 }
339 }
340
341 print " [$check_id_count]";
342
343 ##
344 # Writing results / no results do the database and update the datasets in the
345 # data and direction.
346 # Meaning of i values:
347 # 1 => no result found
348 # 3 => part of direction result
349 # 4 => part of data result
350 ##
351
352 $check_id_count = 0;
353
354 print "\n\t=> Writing results to database (+ = result typ 3, & = result typ 4, o = overlap (no result), - = no result):\n\t";
355 foreach my $res_key( keys(%$rfidresults) ) {
356
357 ##
358 # getting 'in' ids to update
359 my $res_item = $rfidresults->{$res_key};
360 my $inid = $res_item->{'in'}->{'id'};
361 my $inid_innerdataid = $res_item->{'in'}->{'innerdataid'};
362 my $inid_outerdatait = $res_item->{'in'}->{'outerdataid'};
363 my ($outid, $outid_innerdataid, $outid_outerdatait);
364
365 ##
366 # if an 'out' part is present, get the values
367 if( defined $res_item->{'out'} ) {
368 $outid = $res_item->{'out'}->{'id'};
369 $outid_innerdataid = $res_item->{'out'}->{'innerdataid'};
370 $outid_outerdatait = $res_item->{'out'}->{'outerdataid'};
371 }
372
373 $check_id_count++;
374
375 ##
376 # items for which no data could be found
377 if( !$res_item->{'result'} ) {
378
379 (($check_id_count % 100) == 0) ? print "- [$check_id_count]\n\t" : print "-";
380 # Updating dataset in the direction table
381 &UpdateDirTable( $inid, $inid, 1 );
382
383 $NORESCOUNT++;
384
385 ##
386 # results with overlap (not a result)
387 } elsif( $res_item->{'overlap'} ) {
388
389 (($check_id_count % 100) == 0) ? print "o [$check_id_count]\n\t" : print "o";
390 if($res_item->{'typ'} == 3) {
391 # Updating datasets in the direction table
392 &UpdateDirTable( $inid, $outid, 1);
393
394 } else {
395 # Updating datasets in the direction table
396 &UpdateDirTable( $inid, $inid, 1);
397 }
398
399 $OVERLAPS++;
400 $NORESCOUNT++;
401
402 ##
403 # results without overlap -> insert into result table
404 } elsif( $res_item->{'result'} && !$res_item->{'overlap'} ) {
405 if($rfidresults->{$res_key}->{'typ'} == 3) {
406 (($check_id_count % 100) == 0) ? print "+ [$check_id_count]\n\t" : print "+";
407 # Updating datasets in the direction table
408 &UpdateDirTable($inid, $outid, 3);
409 # Updating datasets in the data table
410 &UpdateDataTable($inid_innerdataid,$inid_outerdatait,$outid_innerdataid,$outid_outerdatait, 3);
411 #print "\$res_item => " . Dumper($res_item) ."\n";
412
413 $DIRRESCOUNT++
414
415 } else {
416 (($check_id_count % 100) == 0) ? print "& [$check_id_count]\n\t" : print "&";
417 # Updating datasets in the direction table
418 &UpdateDirTable($inid, $inid, 4);
419 # Updating datasets in the data table
420 &UpdateDataTable($inid_innerdataid,$inid_outerdatait,$outid_innerdataid,$outid_outerdatait, 4);
421
422 $DATARESCOUNT++;
423 }
424
425 print RES &InsertResult( $res_item );
426 $RESCOUNT++;
427
428 } else {
429 die(print "\$res_item => " . Dumper($res_item) ."\n");
430 }
431
432 }
433
434 print " [$check_id_count]\n";
435
436 print "\t=> Marking out results which were not used:\n\t";
437 $check_id_count = 0;
438 foreach my $out_key (@RFIDDIROUTDATASK) {
439 $check_id_count++;
440 (($check_id_count % 100) == 0) ? print ". [$check_id_count]\n\t" : print ".";
441 &UpdateDirTable($out_key, $out_key, 1);
442 }
443 print " [$check_id_count]\n";
444
445 ##
446 # update the rfids table to indicate that this rfid has been searched for in/out pairs
447 $DBH->do(qq{UPDATE `$TABLE_RFIDS` set i='3', res=NOW() WHERE id = '$rfid'})
448 or die("Could not update $TABLE_RFIDS -> $rfid: " . $DBH->errstr);
449
450
451 ##
452 # uncomment next line to test only one rfid
453 #exit;
454
455}
456
457########################################################################
458# ENDING
459
460$DBH->disconnect();
461
462my $summary = qq{
463================================================
464rfids:\t\t\t\t$RFIDCOUNT
465Datasets:\t\t\t$DATACOUNT
466Results (direction/data):\t$RESCOUNT ($DIRRESCOUNT/$DATARESCOUNT)
467No Results (overlaps):\t\t$NORESCOUNT ($OVERLAPS)
468================================================
469};
470
471printf RES $summary;
472close(RES);
473
474print $summary;
475
476print"\n================================================\n";
477print"SEARCHRES.PL COMPLETE";
478print"\n================================================\n";
479
480my @args = ( "/usr/bin/perl -I$SCRIPT_PATH " . $SCRIPT_PATH."meetings.pl");
481system(@args) == 0
482 or die "system @args failed: $?";
483exit;
484
485
486########################################################################
487# SUBS
488########################################################################
489##
490# Check for matching out data (from the direction out data and the data out data)
491sub SearchMatchOut {
492
493 my ($rfid, $toCheckItem) = @_;
494
495 ##
496 # setting up return values
497 my $result= { 'in' => $toCheckItem };
498
499 ##
500 # get matching direction and data out datasets
501 my $dirMatch = &SearchMatchDir($toCheckItem); # this gives the results which are like (1-3) -> (3-1) ( in pair/out pair pairs)
502 my $dataMatch = &SearchMatchData($toCheckItem); # this gives the results which are like (1-3) -> 1) ( in pair / out )
503
504 ##
505 # If we have no keys in $dirMatch and $dataMatch we have no matching out data.
506 return $result if (scalar(keys %$dirMatch) == 0) && (scalar(keys %$dataMatch) == 0);
507
508 ##
509 # Else we have either a direction or a data result.
510 #
511 # Time min returns 1 if the dirMatch is the one closer to the IN time
512 # and 0 if the dataMatch is the one which is closer to the IN time.
513 # This works as well if one of the values is undefined.
514 if( &TimeMin($dirMatch, $dataMatch) ) {
515 return &DirMatchResult($result, $dirMatch);
516 } else {
517 return &DataMatchResult($result, $dataMatch);
518 }
519
520}
521
522##
523# Put together a direction match result (1-3) -> (3-1)
524sub DirMatchResult {
525 my ($result, $dirMatch) = @_;
526
527 $result->{'out'} = $dirMatch->{ shift(@{[keys %$dirMatch]}) };
528 $result->{'typ'} = 3; # If the out match is a dataset from the direction table, set the typ to 3
529
530 return $result;
531}
532
533##
534# Put together a data match result (1-3) -> (3-1)
535sub DataMatchResult {
536 my ($result, $dataMatch) = @_;
537
538 $result->{'out'} = $dataMatch->{ shift(@{[keys %$dataMatch]}) };
539 $result->{'typ'} = 4; # If the out match is a dataset from the data table, set the typ to 4
540
541 return $result;
542}
543
544
545##
546# search for matching direction out result (box).
547sub SearchMatchDir {
548
549 my $toCheckItem = shift(@_);
550 my $dirMatch = ();
551
552 ##
553 # Search for a matching direction out result item in TIME.
554 for (my $i = 0; $i < scalar(@RFIDDIROUTDATASK); $i++) {
555
556 # jump to next if the time is before the time we check for.
557 next if $RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}->{'unix_time'} < $toCheckItem->{'unix_time'};
558
559 ##
560 # if the time is after the time time for the data we check for, and the box
561 # is the same, we've found a direction result (matching OUT data).
562 if ($RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}->{'box'} eq $toCheckItem->{'box'}) {
563 $dirMatch->{$RFIDDIROUTDATASK[$i]} = $RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}; # Found a match
564 $dirMatch->{$RFIDDIROUTDATASK[$i]}->{'index'} = $i; # Store index for later deletion
565 return $dirMatch;
566 }
567
568 }
569
570 return $dirMatch;
571
572}
573
574##
575# search for matching direction out data (Antenna [box]1).
576sub SearchMatchData {
577
578 my $toCheckItem = shift(@_);
579 my $dataMatch = ();
580
581 ##
582 # Search for a matching data item in TIME.
583 for (my $i = 0; $i < scalar(@RFIDOUTDATASK); $i++) {
584
585 # jump to next if the time is before the time we check for.
586 next if $RFIDOUTDATA->{$RFIDOUTDATASK[$i]}->{'unix_time'} < $toCheckItem->{'unix_time'};
587
588 ##
589 # if the time is after the time time for the data we check for, and the box
590 # is the same, we've found a DATA result (matching ANTENNA OUT data).
591 if ($RFIDOUTDATA->{$RFIDOUTDATASK[$i]}->{'box'} eq $toCheckItem->{'box'}) {
592 $dataMatch->{$RFIDOUTDATASK[$i]} = $RFIDOUTDATA->{$RFIDOUTDATASK[$i]}; # Found a match
593 $dataMatch->{$RFIDOUTDATASK[$i]}->{'index'} = $i; # Store index for later deletion
594 return $dataMatch;
595 }
596
597 }
598
599 return $dataMatch;
600}
601
602##
603# Insert a result entry into the database.
604#
605# Update the direction / data table to mark the datasets as used in a result dataset.
606#
607sub InsertResult {
608
609 my $res = shift;
610
611
612 ##
613 # extracting rfid/table/kind of res data
614 my $rfid = $res->{'in'}->{'rfid'}; # get the rfid name
615 my $typ = $res->{'typ'}; # get the result typ
616 my $nerv_index = $res->{'nerv_index'}; # index of nervousness
617
618 ############################################
619 # extracting "in" data
620 my $inId = $res->{'in'}->{'id'};
621 my $box = $res->{'in'}->{'box'};
622 my $box_in = $res->{'in'}->{'time'};
623 my $inInnerId = $res->{'in'}->{'innerdataid'};
624 my $inOuterId = $res->{'in'}->{'outerdataid'};
625
626 ############################################
627 # extracting "out" data
628
629 my $outId = $res->{'out'}->{'id'};
630 my $box_out = $res->{'out'}->{'time'};
631 my $outInnerId = $res->{'out'}->{'innerdataid'};
632 my $outOuterId = $res->{'out'}->{'outerdataid'};
633
634 ##
635 # calculate the time spent in the box in seconds
636 my $dt = $res->{'out'}->{'unix_time'} - $res->{'in'}->{'unix_time'};
637
638 ############################################
639 # insert result into result table
640 $INSRESSTH->execute($rfid, $box, $box_in, $box_out, $dt, $inId, $outId, $typ, $nerv_index)
641 or die("Could not insert into $TABLE_RES: " . $DBH->errstr);
642
643 ##
644 # get insert id and update the data.res_id and dir.res_id tables
645 my $res_id = $DBH->{ q{mysql_insertid} };
646
647 ##
648 # update the data table
649 $UPDATEDATARESIDS->execute($res_id, $inInnerId, $inOuterId, $outInnerId, $outOuterId)
650 or die( "Could not update $TABLE_DATA: " . $DBH->errstr );
651
652 ##
653 # update the data table
654 $UPDATEDIRRESIDS->execute($res_id, $inId, $outId)
655 or die( "Could not update $TABLE_DIR: " . $DBH->errstr );
656
657 ##
658 # return a result entry for the log File
659 my $dtStr = sprintf "%02d:%02d:%02d",(gmtime $dt)[2,1,0];
660 return sprintf($PRINTF,$rfid,$box,$box_in,$box_out,$dtStr);
661}
662
663##
664# Update the i values of the data datasets.
665#
666# Set 'i' values on data table data
667#
668sub UpdateDataTable {
669 my ($inInnerId,$inOuterId,$outInnerId,$outOuterId,$i) = @_;
670
671 die("UpdateDataTable => \$inInnerId is not defined") if !defined $inInnerId;
672 die("UpdateDataTable => \$inOuterId is not defined ") if !defined $inOuterId;
673 die("UpdateDataTable => \$outInnerId is not defined") if !defined $outInnerId;
674 die("UpdateDataTable => \$outOuterId is not defined") if !defined $outOuterId;
675 die("UpdateDataTable => \$i is not defined") if !defined $i;
676
677 $UPDATEDATATABLE->execute($i,$inInnerId,$inOuterId,$outInnerId,$outOuterId)
678 or die("Could not update $TABLE_DATA: " . $DBH->errstr);
679}
680
681##
682# Update the i values of the direction datasets.
683#
684# Set 'i' values on direction table data
685# hier irgendwo
686sub UpdateDirTable {
687 my ($id_one, $id_two, $i) = @_;
688
689 die("UpdateDirTable => \$id_one is not defined") if !defined $id_one;
690 die("UpdateDirTable => \$id_two is not defined ") if !defined $id_two;
691 die("UpdateDirTable => \$i is not defined") if !defined $i;
692
693 $UPDATEDIRTABLE->execute($i, $id_one, $id_two)
694 or die("Couldn't update $TABLE_DIR: " . $DBH->errstr );
695}
696
697##
698# Get the minimal (unix-) time of the two results.
699#
700# If the direction match time is smaller or equal compared to the data match time
701# return 0 else 1
702sub TimeMin {
703
704 my ($dirMatch, $dataMatch) = @_;
705
706 return 1 if scalar(keys(%$dataMatch) == 0); # 1 if we have no data match
707 return 0 if scalar(keys(%$dirMatch) == 0); # 0 if we have no direction match
708
709 my $dirTime = $dirMatch->{ shift(@{[keys %$dirMatch]}) }->{'unix_time'};
710 my $dataTime= $dataMatch->{ shift(@{[keys %$dataMatch]}) }->{'unix_time'};
711
712 return $dirTime <= $dataTime ? 1 : 0;
713}
714
715##
716# Delete the passed index from the passed array and return the new array.
717sub DelItemFromArray {
718 my ($index, $arr) = @_;
719
720 splice(@$arr, $index, 1);
721
722 return $arr;
723}
724
725##
726# select statement return a hash ref with the results and the 'id' field as the key
727sub SelArrayRef {
728
729 my ($sth, $fields, $params) = @_;
730 my $rfidData = {};
731
732 $sth->execute(@$params)
733 || die("Could not execute statement: " . $DBH->errstr);;
734
735 my %rec = ();
736 $sth->bind_columns( map { \$rec{$_} } @$fields )
737 || die("Could not bind columns: " . $DBH->errstr);
738 while ( $sth->fetchrow_arrayref ) {
739 $rfidData->{ $rec{'id'} } = {%rec};
740 }
741 return ($rfidData);
742
743}
744
745##
746# Search for overlapping results for a given result key
747sub searchforOverlap {
748
749 my ($res_sorted_key, $rfid_results) = @_;
750
751 my @overlaps = ();
752 my $i = 0;
753
754 ##
755 # if the key's array consists only of one element we stop testing immediately
756 # This happens when just one element ($res_sorted_key) is in the $res_sorted array.
757 return \@overlaps if scalar(@RFIDRESSORTEDSK) == 0;
758
759 ##
760 # Check if the entry time ('in') of the next result - sorted by time - is less then the exit time ('out')
761 # of the result we check. If that's the case, these result we check is overlaping with the next 'in' result.
762 while( $rfid_results->{$res_sorted_key}->{'out'}->{'unix_time'} > $rfid_results->{ $RFIDRESSORTEDSK[$i] }->{'in'}->{'unix_time'} ) {
763
764 push(@overlaps, $RFIDRESSORTEDSK[$i] );
765
766 ##
767 # if i is the same as the key's array length, get out the loop.
768 $i++;
769 last if $i == scalar(@RFIDRESSORTEDSK);
770 }
771
772 return \@overlaps;
773}
774
775##
776# Search for other antenna readings that were recorded while the result
777sub searchOtherReadings {
778
779 my ($res_value_sorted_key, $rfid_results) = @_;
780 my $res_value = $rfid_results->{$res_value_sorted_key};
781 my $dataset_overlaps = {};
782
783 if($res_value->{'typ'} == 3) {
784
785 $READINGS_DURING_DIRECTION_RESULT->execute( $res_value->{'out'}->{'innerdataid'}, $res_value->{'in'}->{'rfid'}, $res_value->{'in'}->{'time'}, $res_value->{'out'}->{'id'} )
786 or die("Could not execute statement: " . $DBH->errstr);
787
788 $dataset_overlaps = $READINGS_DURING_DIRECTION_RESULT->fetchall_hashref('id');
789
790 } elsif($res_value->{'typ'} == 4) {
791
792 $READINGS_DURING_DATA_RESULT->execute( $res_value->{'in'}->{'rfid'}, $res_value->{'in'}->{'time'}, $res_value->{'out'}->{'time'})
793 or die("Could not execute statement: " . $DBH->errstr);
794
795 $dataset_overlaps = $READINGS_DURING_DATA_RESULT->fetchall_hashref('id');
796
797 }
798
799 return $dataset_overlaps;
800}
801
802##
803# check if the dataset overlaps hash contains only readings at
804# antenna 3 of the same box (nervous mouse looking out)
805sub checkNervousness {
806
807 my ($res_sorted_key, $rfid_results, $dataset_overlaps) = @_;
808
809 my $res_value = $rfid_results->{$res_sorted_key};
810 my $ant_to_check = $res_value->{'in'}->{'box'} . '3';
811
812 for my $overlap_key ( keys %$dataset_overlaps) {
813
814 if($dataset_overlaps->{$overlap_key}->{'ant'} != $ant_to_check) {
815 return 0;
816 }
817 }
818
819
820 return 1;
821
822}
823
824
825##
826# Sort a hash ($data) by a value ($field), and return an array reference with the sorted keys.
827sub sortedHashKeysArray {
828
829 my ($data, $field) = @_;
830
831 my @sortedKeys = ();
832 foreach my $key( sort{ $data->{$a}->{$field} <=> $data->{$b}->{$field} } keys(%$data) ) {
833 push( @sortedKeys, $key);
834 }
835
836 return \@sortedKeys;
837
838}