Skip to content

Instantly share code, notes, and snippets.

@rico rico/searchres.pl
Created Jun 8, 2009

Embed
What would you like to do?
#!/usr/bin/perl -w
##############################################################################
# Searches for results - Step 3 of importing the data.
#
# Continues with 'counter.pl' when finished.
#
# rleuthold@access.ch - 8.1.2009
##############################################################################
use strict;
use DBI;
use Cwd;
use Fcntl;
use Date::Calc qw(Delta_YMDHMS);
use DateTime::Format::Strptime;
use Data::Dumper;
use lib 'lib';
use lib::DBHandler;
use lib::XMLPaths;
use lib::DBTables;
##
# Global variables
my $DBH;
my $TABLES = DBTables->new();
my $PATHS = XMLPaths->new();
my $PERLCONFIG = PerlConfig->new();
# Paths / directories
my $DATA_PATH = $PATHS->get_path('data');
my $SUBFOLDER = $PATHS->get_path('imported');
my $SCRIPT_PATH = $PERLCONFIG->get_scriptsfolder();
# database tables
my $TABLE_DATA = $TABLES->get_table_name('data');
my $TABLE_RFIDS = $TABLES->get_table_name('rfids');
my $TABLE_DIR = $TABLES->get_table_name('direction_results');
my $TABLE_RES = $TABLES->get_table_name('results');
my $DAYS_TO_COUNT_TABLE = $TABLES->get_days_to_count_table();
my $RFIDDIRINDATA = ();
my @RFIDDIRINDATASK = ();
my $RFIDDIROUTDATA = ();
my @RFIDDIROUTDATASK = ();
my $RFIDOUTDATA = ();
my @RFIDOUTDATASK = ();
my @RFIDRESSORTEDSK = ();
# some counters
my $DATACOUNT = 0; # number of (in) datasets
my $RESCOUNT = 0; # number if results
my $DIRRESCOUNT = 0; # number of direction result
my $DATARESCOUNT = 0; # number of data results
my $NORESCOUNT = 0; # number of in datasets for which no resuult could be found
my $OVERLAPS = 0; # number of overlaps
my $RFIDCOUNT = 0; # number of rfids
#my $BOX_OUT; # store the time from which we continue to search
my $PRINTF = "%-12s%-7s%-22s%-22s%-9s\n";
my $PRINTFSEP = "-----------+------+---------------------+---------------------+--------+\n";
####################################
# PREAMBLE
####################################
print"\n================================================\n";
print "STARTING SEARCHRES.PL\n\n";
print "Symbols:\n";
print "\t+ => direction result (1-3) -> (3-1)\n";
print "\t& => data result (1-3) -> (1)\n";
print "\t- => no result\n";
print "\to => overlap (no result)\n";
print"\n================================================\n";
################################
# set up results file
##
# where should we write the output
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;
my $dayDate = $mday."_". ($mon+1) ."_". ($year+1900) ."/";
my $DAY_FOLDER = $SUBFOLDER . $dayDate;
##
# Try to create folders if they don't exist
mkdir($SUBFOLDER, 0771) unless (-d $SUBFOLDER);
mkdir($DAY_FOLDER, 0771) unless (-d $DAY_FOLDER);
my $filename = $DAY_FOLDER. "res_data\.txt"; # the filename with some kind of timestamp
sysopen (RES, $filename, O_CREAT |O_WRONLY, 0755) or die("Can't open result file : $!");
####################################
# open db connection
$DBH = DBHandler->new()->connect();
######
# specifying/preparing the sql statements which are used many times (for performance reasons)
#my $LASTOUTRFID = $DBH->prepare(qq{SELECT MAX(box_out) FROM `$TABLE_RES` WHEERE rfid = ?})
# or die("Could not prepare statement to last box out: " . $DBH->errstr);
my $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` > ?})
or die( "Could not prepare statement to get rfid dir results: " . $DBH->errstr);
my $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 > ?})
or die( "Could not prepare statement to get rfid dir results: " . $DBH->errstr);
my $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 > ?} )
or die("Could not prepare statement to get rfid data results: " . $DBH->errstr);
my $INSRESSTH = $DBH->prepare(qq{INSERT INTO `$TABLE_RES` (rfid, box, box_in, box_out, dt, inid, outid, i, nerv_index) VALUES( ?, ?, ?, ?, SEC_TO_TIME(?), ?, ?, ?, ?)})
or die("Could not prepare statement to insert result: " . $DBH->errstr);
my $UPDATEDATATABLE = $DBH->prepare(qq{UPDATE $TABLE_DATA SET i= ? WHERE id IN(?,?,?,?)})
or die("Could not prepare statement to update $TABLE_DATA: " . $DBH->errstr);
my $UPDATEDIRTABLE = $DBH->prepare(qq{UPDATE $TABLE_DIR SET i= ? WHERE id IN(?,?)})
or die("Could not prepare statement to update $TABLE_DIR: " . $DBH->errstr);
my $READINGS_DURING_DIRECTION_RESULT = $DBH->prepare(qq{SELECT * FROM $TABLE_DATA WHERE id != ? AND rfid=? AND time > ? AND time <
(SELECT time FROM $TABLE_DATA WHERE id = (SELECT innerdataid FROM $TABLE_DIR WHERE id = ?));
})
or die("Could not prepare statement to check for other readings in table $TABLE_DATA: " . $DBH->errstr);
my $READINGS_DURING_DATA_RESULT = $DBH->prepare(qq{SELECT * FROM $TABLE_DATA WHERE rfid=? AND time > ? AND time < ?})
or die("Could not prepare statement to check for other readings in table $TABLE_DATA: " . $DBH->errstr);
my $UPDATEDATARESIDS = $DBH->prepare(qq{UPDATE $TABLE_DATA SET res_id= ? WHERE id IN(?,?,?,?)})
or die("Could not prepare statement to update $TABLE_DATA: " . $DBH->errstr);
my $UPDATEDIRRESIDS = $DBH->prepare(qq{UPDATE $TABLE_DIR SET res_id= ? WHERE id IN(?,?)})
or die("Could not prepare statement to update $TABLE_DIR: " . $DBH->errstr);
##############################################################
# MAIN
##############################################################
##
#Create 'temporary' table to store the days which have to be counted in the counter.pl script.
$DBH->do(qq{CREATE TABLE IF NOT EXISTS `$DAYS_TO_COUNT_TABLE` (`day` date NOT NULL, PRIMARY KEY (`day`) )})
|| die ("Could not create table '$DAYS_TO_COUNT_TABLE': " . $DBH->errstr);
$DBH->do(qq{INSERT IGNORE INTO $DAYS_TO_COUNT_TABLE (`day`) SELECT DISTINCT DATE(`time`) FROM $TABLE_DIR WHERE i='0'})
|| die ("Could not insert days to count into $DAYS_TO_COUNT_TABLE: " . $DBH->errstr);
##
# getting rfids which are already analyzed for direction
my @RFIDS = map {$_->[0]} @{$DBH->selectall_arrayref("SELECT id FROM $TABLE_RFIDS WHERE i='2'")};
#
# print header text to the result file
printf RES ( $PRINTF,"rfid","box","box_in","box_out","dt");
printf RES $PRINTFSEP;
# Now search for each rfid
foreach my $rfid (@RFIDS) {
$RFIDCOUNT++;
print "[rfid $RFIDCOUNT of " . scalar(@RFIDS). "] => [$rfid]\t";
##
# get the latest box out from the result table to avoid result overlap
my $max_time = shift(@{$DBH->selectcol_arrayref(qq{SELECT MAX(box_out) FROM `$TABLE_RES` WHERE rfid = '$rfid'})}) || '0000-00-00 00:00:00';
##
# Get an array reference with the direction IN data for the rfid, the key is the time in the unix timestamp format
$RFIDDIRINDATA = SelArrayRef($RFIDDIRDATAINSTH, [qw{id rfid time unix_time box dir outerdataid innerdataid}], [$rfid, $max_time]);
# $RFIDDIRDATAINSTH->execute($rfid,$max_time);
# $RFIDDIRINDATA = $RFIDDIRDATAINSTH->fetchall_hashref('id');
##
# Jump to next rfid if we have no IN data to search.
if(scalar keys(%$RFIDDIRINDATA) == 0) {
print "\n\t(no data)\n";
##
# update the rfids table to indicate that this rfid has been searched for in/out pairs
$DBH->do(qq{UPDATE `$TABLE_RFIDS` set i='3', res=NOW() WHERE id = '$rfid'})
or die("Could not update $TABLE_RFIDS -> $rfid: " . $DBH->errstr);
next;
}
##
# Sorted keys for the in data
@RFIDDIRINDATASK = @{&sortedHashKeysArray($RFIDDIRINDATA, 'unix_time')};
$DATACOUNT += scalar(@RFIDDIRINDATASK);
#
# Get an array reference with the direction OUT data for the rfid, the key is the time in the unix timestamp format
$RFIDDIROUTDATA = SelArrayRef($RFIDDIRDATAOUTSTH, [qw{id time unix_time box dir outerdataid innerdataid}], [$rfid, $max_time]);
##
# Sorted keys for the OUT data
@RFIDDIROUTDATASK = @{&sortedHashKeysArray($RFIDDIROUTDATA, 'unix_time')};
##
# Get an array reference with the OUT data for the rfid, the key is the time in the unix timestamp format
$RFIDOUTDATA = SelArrayRef($RFIDOUTDATASTH, [qw{id time unix_time box outerdataid innerdataid}], [$rfid, $max_time]);
##
# Sorted keys for the data data
@RFIDOUTDATASK = @{&sortedHashKeysArray($RFIDOUTDATA, 'unix_time')};
##
# main loop
my $rfidresults = {};
my $rfid_no_results = {};
@RFIDRESSORTEDSK = ();
##
# Search for result pair
my $check_id_count = 0;
print "\n\t=> Getting results:\n\t";
my $rfid_data_count = scalar(@RFIDDIRINDATASK);
##
# Looping through the IN direction data for the rfid and search for matching OUT data.
while (scalar(@RFIDDIRINDATASK) > 0 ) {
##
# Getting matching OUT result for the IN result defined by the $toCheckId.
my $toCheckId = shift(@RFIDDIRINDATASK);
my $result = &SearchMatchOut($rfid, $RFIDDIRINDATA->{$toCheckId});
##
# SearchMatchOut returns a hash reference with an 'in' and an out 'key' with the respective values.
# If we have no 'out' key, no matching out result could be found.
if( defined $result->{'out'} ) {
$result->{'result'} = 1;
$rfidresults->{$toCheckId} = $result; # add the result to the hash with the results for this rfid
##
# Delete `used` direction 'out' key from the array with the sorted keys for the result type.
if($result->{'typ'} == 3) { # direction result
@RFIDDIROUTDATASK = @{&DelItemFromArray($result->{'out'}->{'index'}, \@RFIDDIROUTDATASK)};
} else { # data result
@RFIDOUTDATASK = @{&DelItemFromArray($result->{'out'}->{'index'},\@RFIDOUTDATASK)};
}
push(@RFIDRESSORTEDSK, $toCheckId);
##
# No matching 'out' data could be found for this id
} else {
$result->{'result'} = 0;
$rfidresults->{$toCheckId} = $result;
}
$check_id_count++;
(($check_id_count % 100) == 0) ? print ". [$check_id_count]\n\t" : print ".";
##
# uncomment next line to check only one id.
#exit;
}
print " [$rfid_data_count]";
##
# After finding the result pairs we check for overlapping (time) results
##
# Loop through results and check for overlaps.
$check_id_count = 0;
print "\n\t=> Solving overlap conflicts (+ = result overlap, * = dataset overlap, & = nervous, - = no conflict):\n\t";
while (scalar(@RFIDRESSORTEDSK) > 0) {
my $res_sorted_key = shift(@RFIDRESSORTEDSK);
my $res_sorted_key_overlap = searchforOverlap($res_sorted_key, $rfidresults);
$check_id_count++;
##
# resolve the overlap conflict (mark the overlaps)
if ( scalar (@$res_sorted_key_overlap) > 0 ) {
(($check_id_count % 100) == 0) ? print "+ [$check_id_count]\n\t" : print "+";
##
# case 1: The result in question overlaps many others.
#
# The chance of one `wrong` result (missed antenna reading) is
# higher the chance of multiple `false` readings building results together.
# So we mark the result (the one in question) which overlaps the other ones.
if( scalar(@$res_sorted_key_overlap) > 1) {
$rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
##
# case 2: The result in question overlaps just one other.
#
# We skip both results (mark them as overlap).
#
} else {
$rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
$rfidresults->{(@{$res_sorted_key_overlap}[0])}->{'overlap'} = 1;
}
##
# No result overlapping detectected
} else {
##
# Check for DATASET overlapping
$rfidresults->{ $res_sorted_key }->{'overlap'} = 0;
$rfidresults->{ $res_sorted_key }->{'nerv_index'} = 0;
my $dataset_overlaps = &searchOtherReadings($res_sorted_key, $rfidresults);
# if we have dataset overlapping we check if the overlapped datasets are readings at the inner antenna (3)
# of the box this result takes place.
# If this is the case, this is a 'nervous result', because the mouse is checking the entry tube quite often.
# The nerv_index value specifies the number of readings at tthe inner antenna
# (some kind of measurement of how nervous the mouse is)
if(keys %$dataset_overlaps) {
if ( &checkNervousness($res_sorted_key, $rfidresults, $dataset_overlaps) ) {
(($check_id_count % 100) == 0) ? print "& [$check_id_count]\n\t" : print "&";
$rfidresults->{ $res_sorted_key }->{'nerv_index'} = scalar(keys %$dataset_overlaps);
} else {
(($check_id_count % 100) == 0) ? print "* [$check_id_count]\n\t" : print "*";
$rfidresults->{ $res_sorted_key }->{'overlap'} = 1;
}
# no dataset overlapping
} else {
(($check_id_count % 100) == 0) ? print "- [$check_id_count]\n\t" : print "-";
$rfidresults->{ $res_sorted_key }->{'overlap'} = 0;
}
}
}
print " [$check_id_count]";
##
# Writing results / no results do the database and update the datasets in the
# data and direction.
# Meaning of i values:
# 1 => no result found
# 3 => part of direction result
# 4 => part of data result
##
$check_id_count = 0;
print "\n\t=> Writing results to database (+ = result typ 3, & = result typ 4, o = overlap (no result), - = no result):\n\t";
foreach my $res_key( keys(%$rfidresults) ) {
##
# getting 'in' ids to update
my $res_item = $rfidresults->{$res_key};
my $inid = $res_item->{'in'}->{'id'};
my $inid_innerdataid = $res_item->{'in'}->{'innerdataid'};
my $inid_outerdatait = $res_item->{'in'}->{'outerdataid'};
my ($outid, $outid_innerdataid, $outid_outerdatait);
##
# if an 'out' part is present, get the values
if( defined $res_item->{'out'} ) {
$outid = $res_item->{'out'}->{'id'};
$outid_innerdataid = $res_item->{'out'}->{'innerdataid'};
$outid_outerdatait = $res_item->{'out'}->{'outerdataid'};
}
$check_id_count++;
##
# items for which no data could be found
if( !$res_item->{'result'} ) {
(($check_id_count % 100) == 0) ? print "- [$check_id_count]\n\t" : print "-";
# Updating dataset in the direction table
&UpdateDirTable( $inid, $inid, 1 );
$NORESCOUNT++;
##
# results with overlap (not a result)
} elsif( $res_item->{'overlap'} ) {
(($check_id_count % 100) == 0) ? print "o [$check_id_count]\n\t" : print "o";
if($res_item->{'typ'} == 3) {
# Updating datasets in the direction table
&UpdateDirTable( $inid, $outid, 1);
} else {
# Updating datasets in the direction table
&UpdateDirTable( $inid, $inid, 1);
}
$OVERLAPS++;
$NORESCOUNT++;
##
# results without overlap -> insert into result table
} elsif( $res_item->{'result'} && !$res_item->{'overlap'} ) {
if($rfidresults->{$res_key}->{'typ'} == 3) {
(($check_id_count % 100) == 0) ? print "+ [$check_id_count]\n\t" : print "+";
# Updating datasets in the direction table
&UpdateDirTable($inid, $outid, 3);
# Updating datasets in the data table
&UpdateDataTable($inid_innerdataid,$inid_outerdatait,$outid_innerdataid,$outid_outerdatait, 3);
#print "\$res_item => " . Dumper($res_item) ."\n";
$DIRRESCOUNT++
} else {
(($check_id_count % 100) == 0) ? print "& [$check_id_count]\n\t" : print "&";
# Updating datasets in the direction table
&UpdateDirTable($inid, $inid, 4);
# Updating datasets in the data table
&UpdateDataTable($inid_innerdataid,$inid_outerdatait,$outid_innerdataid,$outid_outerdatait, 4);
$DATARESCOUNT++;
}
print RES &InsertResult( $res_item );
$RESCOUNT++;
} else {
die(print "\$res_item => " . Dumper($res_item) ."\n");
}
}
print " [$check_id_count]\n";
print "\t=> Marking out results which were not used:\n\t";
$check_id_count = 0;
foreach my $out_key (@RFIDDIROUTDATASK) {
$check_id_count++;
(($check_id_count % 100) == 0) ? print ". [$check_id_count]\n\t" : print ".";
&UpdateDirTable($out_key, $out_key, 1);
}
print " [$check_id_count]\n";
##
# update the rfids table to indicate that this rfid has been searched for in/out pairs
$DBH->do(qq{UPDATE `$TABLE_RFIDS` set i='3', res=NOW() WHERE id = '$rfid'})
or die("Could not update $TABLE_RFIDS -> $rfid: " . $DBH->errstr);
##
# uncomment next line to test only one rfid
#exit;
}
########################################################################
# ENDING
$DBH->disconnect();
my $summary = qq{
================================================
rfids:\t\t\t\t$RFIDCOUNT
Datasets:\t\t\t$DATACOUNT
Results (direction/data):\t$RESCOUNT ($DIRRESCOUNT/$DATARESCOUNT)
No Results (overlaps):\t\t$NORESCOUNT ($OVERLAPS)
================================================
};
printf RES $summary;
close(RES);
print $summary;
print"\n================================================\n";
print"SEARCHRES.PL COMPLETE";
print"\n================================================\n";
my @args = ( "/usr/bin/perl -I$SCRIPT_PATH " . $SCRIPT_PATH."meetings.pl");
system(@args) == 0
or die "system @args failed: $?";
exit;
########################################################################
# SUBS
########################################################################
##
# Check for matching out data (from the direction out data and the data out data)
sub SearchMatchOut {
my ($rfid, $toCheckItem) = @_;
##
# setting up return values
my $result= { 'in' => $toCheckItem };
##
# get matching direction and data out datasets
my $dirMatch = &SearchMatchDir($toCheckItem); # this gives the results which are like (1-3) -> (3-1) ( in pair/out pair pairs)
my $dataMatch = &SearchMatchData($toCheckItem); # this gives the results which are like (1-3) -> 1) ( in pair / out )
##
# If we have no keys in $dirMatch and $dataMatch we have no matching out data.
return $result if (scalar(keys %$dirMatch) == 0) && (scalar(keys %$dataMatch) == 0);
##
# Else we have either a direction or a data result.
#
# Time min returns 1 if the dirMatch is the one closer to the IN time
# and 0 if the dataMatch is the one which is closer to the IN time.
# This works as well if one of the values is undefined.
if( &TimeMin($dirMatch, $dataMatch) ) {
return &DirMatchResult($result, $dirMatch);
} else {
return &DataMatchResult($result, $dataMatch);
}
}
##
# Put together a direction match result (1-3) -> (3-1)
sub DirMatchResult {
my ($result, $dirMatch) = @_;
$result->{'out'} = $dirMatch->{ shift(@{[keys %$dirMatch]}) };
$result->{'typ'} = 3; # If the out match is a dataset from the direction table, set the typ to 3
return $result;
}
##
# Put together a data match result (1-3) -> (3-1)
sub DataMatchResult {
my ($result, $dataMatch) = @_;
$result->{'out'} = $dataMatch->{ shift(@{[keys %$dataMatch]}) };
$result->{'typ'} = 4; # If the out match is a dataset from the data table, set the typ to 4
return $result;
}
##
# search for matching direction out result (box).
sub SearchMatchDir {
my $toCheckItem = shift(@_);
my $dirMatch = ();
##
# Search for a matching direction out result item in TIME.
for (my $i = 0; $i < scalar(@RFIDDIROUTDATASK); $i++) {
# jump to next if the time is before the time we check for.
next if $RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}->{'unix_time'} < $toCheckItem->{'unix_time'};
##
# if the time is after the time time for the data we check for, and the box
# is the same, we've found a direction result (matching OUT data).
if ($RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}->{'box'} eq $toCheckItem->{'box'}) {
$dirMatch->{$RFIDDIROUTDATASK[$i]} = $RFIDDIROUTDATA->{$RFIDDIROUTDATASK[$i]}; # Found a match
$dirMatch->{$RFIDDIROUTDATASK[$i]}->{'index'} = $i; # Store index for later deletion
return $dirMatch;
}
}
return $dirMatch;
}
##
# search for matching direction out data (Antenna [box]1).
sub SearchMatchData {
my $toCheckItem = shift(@_);
my $dataMatch = ();
##
# Search for a matching data item in TIME.
for (my $i = 0; $i < scalar(@RFIDOUTDATASK); $i++) {
# jump to next if the time is before the time we check for.
next if $RFIDOUTDATA->{$RFIDOUTDATASK[$i]}->{'unix_time'} < $toCheckItem->{'unix_time'};
##
# if the time is after the time time for the data we check for, and the box
# is the same, we've found a DATA result (matching ANTENNA OUT data).
if ($RFIDOUTDATA->{$RFIDOUTDATASK[$i]}->{'box'} eq $toCheckItem->{'box'}) {
$dataMatch->{$RFIDOUTDATASK[$i]} = $RFIDOUTDATA->{$RFIDOUTDATASK[$i]}; # Found a match
$dataMatch->{$RFIDOUTDATASK[$i]}->{'index'} = $i; # Store index for later deletion
return $dataMatch;
}
}
return $dataMatch;
}
##
# Insert a result entry into the database.
#
# Update the direction / data table to mark the datasets as used in a result dataset.
#
sub InsertResult {
my $res = shift;
##
# extracting rfid/table/kind of res data
my $rfid = $res->{'in'}->{'rfid'}; # get the rfid name
my $typ = $res->{'typ'}; # get the result typ
my $nerv_index = $res->{'nerv_index'}; # index of nervousness
############################################
# extracting "in" data
my $inId = $res->{'in'}->{'id'};
my $box = $res->{'in'}->{'box'};
my $box_in = $res->{'in'}->{'time'};
my $inInnerId = $res->{'in'}->{'innerdataid'};
my $inOuterId = $res->{'in'}->{'outerdataid'};
############################################
# extracting "out" data
my $outId = $res->{'out'}->{'id'};
my $box_out = $res->{'out'}->{'time'};
my $outInnerId = $res->{'out'}->{'innerdataid'};
my $outOuterId = $res->{'out'}->{'outerdataid'};
##
# calculate the time spent in the box in seconds
my $dt = $res->{'out'}->{'unix_time'} - $res->{'in'}->{'unix_time'};
############################################
# insert result into result table
$INSRESSTH->execute($rfid, $box, $box_in, $box_out, $dt, $inId, $outId, $typ, $nerv_index)
or die("Could not insert into $TABLE_RES: " . $DBH->errstr);
##
# get insert id and update the data.res_id and dir.res_id tables
my $res_id = $DBH->{ q{mysql_insertid} };
##
# update the data table
$UPDATEDATARESIDS->execute($res_id, $inInnerId, $inOuterId, $outInnerId, $outOuterId)
or die( "Could not update $TABLE_DATA: " . $DBH->errstr );
##
# update the data table
$UPDATEDIRRESIDS->execute($res_id, $inId, $outId)
or die( "Could not update $TABLE_DIR: " . $DBH->errstr );
##
# return a result entry for the log File
my $dtStr = sprintf "%02d:%02d:%02d",(gmtime $dt)[2,1,0];
return sprintf($PRINTF,$rfid,$box,$box_in,$box_out,$dtStr);
}
##
# Update the i values of the data datasets.
#
# Set 'i' values on data table data
#
sub UpdateDataTable {
my ($inInnerId,$inOuterId,$outInnerId,$outOuterId,$i) = @_;
die("UpdateDataTable => \$inInnerId is not defined") if !defined $inInnerId;
die("UpdateDataTable => \$inOuterId is not defined ") if !defined $inOuterId;
die("UpdateDataTable => \$outInnerId is not defined") if !defined $outInnerId;
die("UpdateDataTable => \$outOuterId is not defined") if !defined $outOuterId;
die("UpdateDataTable => \$i is not defined") if !defined $i;
$UPDATEDATATABLE->execute($i,$inInnerId,$inOuterId,$outInnerId,$outOuterId)
or die("Could not update $TABLE_DATA: " . $DBH->errstr);
}
##
# Update the i values of the direction datasets.
#
# Set 'i' values on direction table data
# hier irgendwo
sub UpdateDirTable {
my ($id_one, $id_two, $i) = @_;
die("UpdateDirTable => \$id_one is not defined") if !defined $id_one;
die("UpdateDirTable => \$id_two is not defined ") if !defined $id_two;
die("UpdateDirTable => \$i is not defined") if !defined $i;
$UPDATEDIRTABLE->execute($i, $id_one, $id_two)
or die("Couldn't update $TABLE_DIR: " . $DBH->errstr );
}
##
# Get the minimal (unix-) time of the two results.
#
# If the direction match time is smaller or equal compared to the data match time
# return 0 else 1
sub TimeMin {
my ($dirMatch, $dataMatch) = @_;
return 1 if scalar(keys(%$dataMatch) == 0); # 1 if we have no data match
return 0 if scalar(keys(%$dirMatch) == 0); # 0 if we have no direction match
my $dirTime = $dirMatch->{ shift(@{[keys %$dirMatch]}) }->{'unix_time'};
my $dataTime= $dataMatch->{ shift(@{[keys %$dataMatch]}) }->{'unix_time'};
return $dirTime <= $dataTime ? 1 : 0;
}
##
# Delete the passed index from the passed array and return the new array.
sub DelItemFromArray {
my ($index, $arr) = @_;
splice(@$arr, $index, 1);
return $arr;
}
##
# select statement return a hash ref with the results and the 'id' field as the key
sub SelArrayRef {
my ($sth, $fields, $params) = @_;
my $rfidData = {};
$sth->execute(@$params)
|| die("Could not execute statement: " . $DBH->errstr);;
my %rec = ();
$sth->bind_columns( map { \$rec{$_} } @$fields )
|| die("Could not bind columns: " . $DBH->errstr);
while ( $sth->fetchrow_arrayref ) {
$rfidData->{ $rec{'id'} } = {%rec};
}
return ($rfidData);
}
##
# Search for overlapping results for a given result key
sub searchforOverlap {
my ($res_sorted_key, $rfid_results) = @_;
my @overlaps = ();
my $i = 0;
##
# if the key's array consists only of one element we stop testing immediately
# This happens when just one element ($res_sorted_key) is in the $res_sorted array.
return \@overlaps if scalar(@RFIDRESSORTEDSK) == 0;
##
# Check if the entry time ('in') of the next result - sorted by time - is less then the exit time ('out')
# of the result we check. If that's the case, these result we check is overlaping with the next 'in' result.
while( $rfid_results->{$res_sorted_key}->{'out'}->{'unix_time'} > $rfid_results->{ $RFIDRESSORTEDSK[$i] }->{'in'}->{'unix_time'} ) {
push(@overlaps, $RFIDRESSORTEDSK[$i] );
##
# if i is the same as the key's array length, get out the loop.
$i++;
last if $i == scalar(@RFIDRESSORTEDSK);
}
return \@overlaps;
}
##
# Search for other antenna readings that were recorded while the result
sub searchOtherReadings {
my ($res_value_sorted_key, $rfid_results) = @_;
my $res_value = $rfid_results->{$res_value_sorted_key};
my $dataset_overlaps = {};
if($res_value->{'typ'} == 3) {
$READINGS_DURING_DIRECTION_RESULT->execute( $res_value->{'out'}->{'innerdataid'}, $res_value->{'in'}->{'rfid'}, $res_value->{'in'}->{'time'}, $res_value->{'out'}->{'id'} )
or die("Could not execute statement: " . $DBH->errstr);
$dataset_overlaps = $READINGS_DURING_DIRECTION_RESULT->fetchall_hashref('id');
} elsif($res_value->{'typ'} == 4) {
$READINGS_DURING_DATA_RESULT->execute( $res_value->{'in'}->{'rfid'}, $res_value->{'in'}->{'time'}, $res_value->{'out'}->{'time'})
or die("Could not execute statement: " . $DBH->errstr);
$dataset_overlaps = $READINGS_DURING_DATA_RESULT->fetchall_hashref('id');
}
return $dataset_overlaps;
}
##
# check if the dataset overlaps hash contains only readings at
# antenna 3 of the same box (nervous mouse looking out)
sub checkNervousness {
my ($res_sorted_key, $rfid_results, $dataset_overlaps) = @_;
my $res_value = $rfid_results->{$res_sorted_key};
my $ant_to_check = $res_value->{'in'}->{'box'} . '3';
for my $overlap_key ( keys %$dataset_overlaps) {
if($dataset_overlaps->{$overlap_key}->{'ant'} != $ant_to_check) {
return 0;
}
}
return 1;
}
##
# Sort a hash ($data) by a value ($field), and return an array reference with the sorted keys.
sub sortedHashKeysArray {
my ($data, $field) = @_;
my @sortedKeys = ();
foreach my $key( sort{ $data->{$a}->{$field} <=> $data->{$b}->{$field} } keys(%$data) ) {
push( @sortedKeys, $key);
}
return \@sortedKeys;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.