· 8 years ago · Mar 08, 2018, 03:52 AM
1#!/usr/bin/perl -w
2
3##############################################################################
4# Searches for dirction results in the raw data created by 'logimport.pl'
5# - Step 2 of importing the data.
6#
7# Continues with 'searchres.pl' when finished.
8#
9# rleuthold@access.ch - 8.1.2009
10##############################################################################
11
12use strict;
13use DBI;
14use Cwd;
15use Fcntl;
16use Date::Calc qw(:all);
17use Data::Dumper;
18
19use lib 'lib';
20use lib::DBHandler;
21use lib::XMLPaths;
22use lib::DBTables;
23
24##############################################################################
25##
26# Global variables
27my $DBH;
28my $TABLES = DBTables->new();
29my $PATHS = XMLPaths->new();
30my $PERLCONFIG = PerlConfig->new();
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 $DAYS_TO_COUNT_TABLE = $TABLES->get_days_to_count_table();
42
43my $INTERVAL = $PERLCONFIG->get_antennainterval(); # Max time in seconds between the two antenna readings
44 # at a box to form a valid direction results
45my $RFIDDATA;
46my @RFIDDATASORTEDKEYS; # sort
47
48my $PRINTF = "%-12s%-7s%-22s%-4s\n";
49my $PRINTFSEP = "-----------+------+---------------------+---+\n";
50
51##############################################################################
52##
53# some counters
54my $RESCOUNT = 0;
55my $NORESCOUNT = 0;
56my $IDCOUNT = 0;
57my $RFIDCOUNT = 0;
58my $RFIDDATAKEYCOUNT = 0;
59my $SAME_TIME = 0;
60
61####################################
62# PREAMBLE
63####################################
64print"\n================================================\n";
65print"STARTING SEARCHDIR.PL\n\n";
66print "Symbols:\n";
67print "\t+ => direction result (1-3) -> (3-1)\n";
68print "\t- => no result\n";
69print"\n================================================\n";
70################################
71# set up results file
72my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
73 localtime time;
74my $dayDate = $mday . "_" . ( $mon + 1 ) . "_" . ( $year + 1900 ) . "/";
75my $DAY_FOLDER = $DATA_PATH . $SUBFOLDER . $dayDate;
76$DAY_FOLDER = $SUBFOLDER . $dayDate;
77mkdir( $DAY_FOLDER, 0771 ) unless ( -d $DAY_FOLDER );
78my $filename = $DAY_FOLDER . "dir_data\.txt"; # the filename with some kind of timestamp
79sysopen( RES, $filename, O_CREAT | O_WRONLY, 0755 )
80 or die("Can't open result file $filename: $!");
81####################################
82# open db connection
83$DBH = DBHandler->new()->connect();
84######
85# specifying/preparing the sql statements which are used so many times (for performance reasons)
86my $SELRFIDSTH = $DBH->prepare( qq{SELECT id,time, (UNIX_TIMESTAMP(time) * 1000 + millisec) as unix_time_milli, ant, import FROM `$TABLE_DATA` WHERE rfid= ? AND i='0'})
87 or die( "Could not prepare statement to get rfid contents: " . $DBH->errstr );
88
89my $INSRESSTH = $DBH->prepare(qq{INSERT INTO `$TABLE_DIR` (rfid,time, box, dir, outerdataid, innerdataid) VALUES( ?, ?, ?, ?, ?, ? )})
90 or die( "Could not prepare statement to insert the res items: " . $DBH->errstr );
91
92my $UPDATERESSTH = $DBH->prepare(qq{UPDATE `$TABLE_DATA` set i=2, dir_id = ? WHERE id IN( ?, ? )})
93 or die( "Could not prepare statement to update the data table items: ". $DBH->errstr );
94
95my $UPDATENORESSTH = $DBH->prepare(qq{UPDATE `$TABLE_DATA` SET i=1 WHERE id= ?})
96 or die( "Could not prepare statement to update the res items: " . $DBH->errstr );
97
98##############################################################
99# MAIN
100##############################################################
101
102##
103#Create 'temporary' table to store the days which have to be counted in the counter.pl script.
104$DBH->do(qq{CREATE TABLE IF NOT EXISTS `$DAYS_TO_COUNT_TABLE` (`day` date NOT NULL, PRIMARY KEY (`day`) )})
105 || die ("Could not create table '$DAYS_TO_COUNT_TABLE': " . $DBH->errstr);
106$DBH->do(qq{INSERT IGNORE INTO $DAYS_TO_COUNT_TABLE (`day`) SELECT DISTINCT DATE(`time`) FROM $TABLE_DATA WHERE i='0'})
107 || die ("Could not insert days to count into $DAYS_TO_COUNT_TABLE: " . $DBH->errstr);
108
109my @RFIDS = map { $_->[0] } @{ $DBH->selectall_arrayref("SELECT id FROM $TABLE_RFIDS WHERE i='0'") };
110
111
112##
113# print header text to the result file
114printf RES ($PRINTF,"rfid","box","time","dir");
115printf RES $PRINTFSEP;
116
117##
118# main loop to search every rfid table
119foreach my $rfid (@RFIDS) { # search each rfid
120 $RFIDCOUNT++;
121 print "[rfid $RFIDCOUNT of " . scalar(@RFIDS). "] => [$rfid]\t";
122
123 #print Dumper($_)."\n";
124 ##
125 # get all the data for this rfid
126 # this is the main hash we play with
127 $RFIDDATA = {};
128 $RFIDDATA = &SelDBtbl($rfid);
129
130 ##
131 # Jump to next rfid if we have no data to search.
132 if(scalar keys(%$RFIDDATA) == 0) {
133 print "\n\t(no data)\n";
134 ##
135 # update the info in the rfid table for the direction analyze
136 $DBH->do(qq{UPDATE $TABLE_RFIDS SET dir=NOW(), i=2 WHERE id='$rfid'})
137 or die( "Could not clear Table $TABLE_RFIDS: " . $DBH->errstr );
138 next;
139 }
140
141 ##
142 # Create an array with the id's sorted by time ascending
143 @RFIDDATASORTEDKEYS = ();
144 foreach my $rfiddatakey( sort{ $RFIDDATA->{$a}->{'unix_time_milli'} <=> $RFIDDATA->{$b}->{'unix_time_milli'} } keys(%$RFIDDATA) ) {
145 push( @RFIDDATASORTEDKEYS, $rfiddatakey);
146 }
147
148 ##
149 # main loop to search within rfid tables
150 $RFIDDATAKEYCOUNT = 0;
151 print "\n\t";
152
153 while ( @RFIDDATASORTEDKEYS > 0) { # search while we have entries in the table
154 $RFIDDATAKEYCOUNT++;
155 my $res_feedback = &GetRes( $rfid, shift(@RFIDDATASORTEDKEYS), \@RFIDDATASORTEDKEYS,$RFIDDATA );
156 printf RES $res_feedback; # get results;
157 }
158
159
160 print " [" . $RFIDDATAKEYCOUNT . "]\n";
161
162 ##
163 # update the info in the rfid table for the direction analyze
164 $DBH->do(qq{UPDATE $TABLE_RFIDS SET dir=NOW(), i=2 WHERE id='$rfid'})
165 or die( "Could not clear Table $TABLE_RFIDS: " . $DBH->errstr );
166 ##
167 # uncomment next line to test only one rfid
168 exit;
169}
170#########################################################
171# Ending
172#########################################################
173# close db and result File
174$DBH->disconnect();
175
176my $summary = qq{
177================================================
178rfids:\t\t$RFIDCOUNT
179Data sets:\t$IDCOUNT
180Results:\t$RESCOUNT
181No Result:\t$NORESCOUNT
182================================================
183};
184printf RES $summary;
185close(RES);
186
187print $summary;
188
189print"\n================================================\n";
190print"SEARCHDIR.PL COMPLETE";
191print"\n================================================\n";
192
193##
194# continue
195#my @args = ( $SCRIPT_PATH . "searchres.pl" );
196#system(@args) == 0
197# or die "system @args failed: $?";
198exit;
199##############################################################
200# SUBS
201##############################################################
202##
203# select id time and box from the rfid table
204sub SelDBtbl {
205 my $rfid = shift;
206 my $rfidData = {};
207 my @fields = (qw(id time unix_time_milli ant import));
208 $SELRFIDSTH->execute($rfid);
209 my %rec = ();
210 $SELRFIDSTH->bind_columns( map { \$rec{$_} } @fields );
211 while ( $SELRFIDSTH->fetchrow_arrayref ) {
212 $rfidData->{ $rec{'id'} } = {%rec};
213 }
214 return ($rfidData);
215}
216##
217# check for the timespan pairs
218sub GetRes {
219 my ($rfid,$toCheckId,$sorted_keys, $rfidData) = @_;
220
221 my $toCheckItem = $rfidData->{$toCheckId};
222 my $toCheckInterval = $toCheckItem->{'unix_time_milli'} + ($INTERVAL * 1000);
223
224 my $i = 0;
225
226 ##
227 # Search for a matching data item in TIME.
228 while(defined(@{$sorted_keys}[$i]) && $rfidData->{@$sorted_keys[$i]}->{'unix_time_milli'} <= $toCheckInterval) {
229
230 my $possibleMatch = $rfidData->{@$sorted_keys[$i]};
231 if($toCheckItem->{'unix_time_milli'} == $possibleMatch->{'unix_time_milli'}) {
232 $SAME_TIME++;
233 return ( &NoRes($toCheckId) );
234 } elsif ($toCheckItem->{'unix_time_milli'} > $possibleMatch->{'unix_time_milli'}) {
235 die("Next item is before the item to check: tocheckItem => " . Dumper($toCheckItem) ." possibleMatch => " . Dumper($possibleMatch) . "\n");
236 }
237
238 ##
239 # Search for matching ANTENNA data item.
240 if( &CheckAnt( $toCheckItem, $possibleMatch) ) {
241 ##
242 # We found a matching ANTENNA (and time)
243
244 # Delete the matching key from the @RFIDDATASORTEDKEYS
245 @RFIDDATASORTEDKEYS = @{&DelItemFromArray($i, \@RFIDDATASORTEDKEYS)};
246
247 $IDCOUNT += 2;
248 (($RFIDDATAKEYCOUNT % 100) == 0 && $RFIDDATAKEYCOUNT != 0) ? print "+ [$RFIDDATAKEYCOUNT]\n\t" : print "+";
249 return ( &Res($rfid, $toCheckItem, $possibleMatch) ); # handle the results
250
251 }
252
253 $i++;
254 }
255
256 ##
257 # No matching data found - no result
258 (($RFIDDATAKEYCOUNT % 100) == 0 && $RFIDDATAKEYCOUNT != 0) ? print "- [$RFIDDATAKEYCOUNT]\n\t" : print "-";
259 $IDCOUNT++;
260 return ( &NoRes($toCheckId) );
261
262}
263##
264# Check for matching antenna.
265#
266# returns 1 for a match, else 0
267sub CheckAnt {
268 my ( $toCheck, $timePoss ) = @_;
269
270# print "CheckAnt:\n";
271# print "\$toCheck => " . Dumper($toCheck) ."\n";
272# print "\$timePoss => " . Dumper($timePoss) ."\n";
273# sleep(10);
274
275 abs($toCheck->{'ant'} - $timePoss->{'ant'}) == 2 ? return 1 : return 0;
276
277}
278
279##
280# Get Direction for a result.
281#
282# returns 'out' or 'in'
283sub GetDir {
284 my ( $dataset_one, $dataset_two) = @_;
285
286 $dataset_one->{'ant'} - $dataset_two->{'ant'} == 2 ? return 'out' : return 'in';
287}
288
289##
290# insert result into the $TABLE_DIR table and update the indicators in the rfid table
291sub Res {
292 my ( $rfid, $dataset_one, $dataset_two) = @_;
293
294 my $direction = &GetDir($dataset_one, $dataset_two);
295
296
297 # ids/time
298 ##
299 my $box = substr( $dataset_one->{'ant'}, 0, 2 );
300 my ( $outerid, $innerid, $import, $time );
301 if ( $direction eq 'in' ) {
302 $innerid = $dataset_two->{'id'};
303 $outerid = $dataset_one->{'id'};
304 $time = $dataset_one->{'time'};
305
306 } else {
307 $innerid = $dataset_one->{'id'};
308 $outerid = $dataset_two->{'id'};
309 $time = $dataset_two->{'time'};
310 }
311
312 ##
313 # insert result into direction result table
314 $INSRESSTH->execute($rfid, $time, $box, $direction, $outerid, $innerid)
315 or die( "Could not insert into $TABLE_DIR: " . $DBH->errstr );
316
317 ##
318 # get insert id
319 my $dir_id = $DBH->{ q{mysql_insertid} };
320
321 ##
322 # update the data table - more exactly: set the indicator (i) to 2 cause these two items build a direction pair together
323 $UPDATERESSTH->execute($dir_id, $innerid, $outerid)
324 or die( "Could not update $TABLE_DATA: " . $DBH->errstr );
325
326 ##
327 $RESCOUNT += 2;
328
329 return sprintf($PRINTF, $rfid, $box, $time, $direction);
330}
331##
332# update the indicator (i) in the rfid table
333sub NoRes {
334 my $noResId = shift;
335
336 $UPDATENORESSTH->execute($noResId)
337 or die "Couldn't update table $TABLE_DATA : " . $DBH->errstr;
338
339 ##
340 $NORESCOUNT++;
341 return ("");
342}
343##
344# If we have multiple possible results, get the one with the lowest millisec value.
345sub MultiRes {
346 my $multiRes = shift;
347
348 my $lowest_val_key = (sort {$multiRes->{$a}->{'millisec'} <=> $multiRes->{$b}->{'millisec'}} keys %$multiRes)[0];
349 return ( $multiRes->{$lowest_val_key});
350
351}
352##
353# Delete the passed index from the passed array and return the new array.
354sub DelItemFromArray {
355
356 my ($index, $arr) = @_;
357
358 splice(@$arr, $index, 1);
359 return $arr;
360}