· 8 years ago · Mar 08, 2018, 03:54 AM
1#!/usr/bin/perl -w
2
3##############################################################################
4# This scripts updates the box and ant tables
5# - data count for each antenna and box
6# - the time with the last dataset (if older then 1 Week, maybe defect)
7#
8# The script can be started with the '--all' arguments to count for all
9# days in the '$TABLE_DATA' table,
10# instead of just the days in the $DAYS_TO_COUNT_TABLE table,
11# or/and with the '--quiet' argument for suppress script output.
12#
13# rleuthold@access.ch - 9.3.2009
14##############################################################################
15use strict;
16
17use lib 'lib';
18use lib::DBHandler;
19use lib::XMLPaths;
20use lib::DBTables;
21use IO::File;
22use Data::Dumper;
23use Getopt::Long;
24
25##############################################################################
26##
27# Global variables
28my $DBH;
29my $TABLES = DBTables->new();
30my $PATHS = XMLPaths->new();
31my $PERLCONFIG = PerlConfig->new();
32
33# Paths / directories
34my $DATA_PATH = $PATHS->get_path('data');
35my $IMPORTED_FOLDER = $PATHS->get_path('imported');
36
37my $SCRIPT_PATH = $PERLCONFIG->get_scriptsfolder();
38
39# database tables
40my $TABLE_RFIDS = $TABLES->get_table_name('rfids');
41my $TABLE_DIR = $TABLES->get_table_name('direction_results');
42my $TABLE_RES = $TABLES->get_table_name('results');
43my $TABLE_BOX = $TABLES->get_table_name('boxes');
44my $TABLE_DATA = $TABLES->get_table_name('data');
45my $TABLE_ANT = $TABLES->get_table_name('antennas');
46my $TABLE_RFIDS_COUNT = $TABLES->get_table_name('rfid_count');
47my $TABLE_BOXES_COUNT = $TABLES->get_table_name('box_count');
48my $TABLE_ANTS_COUNT = $TABLES->get_table_name('antenna_count');
49
50##
51# The table containing the days to count. This table is created in the logimport.pl script
52# and will be dropped and the end of this script.
53my $DAYS_TO_COUNT_TABLE = $TABLES->get_days_to_count_table();
54
55##
56# The folder with the daily log files
57my $DAY_FOLDER;
58
59my @DAYS_TO_COUNT;
60my $ALL_DAYS = 0;
61my $QUIET = 0;
62
63####################################
64# PREAMBLE
65####################################
66
67##
68# Getting command line options
69GetOptions("quiet"=>\$QUIET, "all"=>\$ALL_DAYS);
70
71
72
73if( $QUIET == 0 ) {
74
75 print"\n================================================\n";
76 print"STARTING COUNTER.PL";
77 print"\n================================================\n";
78}
79
80#############################################################################
81# open db connection
82$DBH = DBHandler->new()->connect();
83#############################################################################
84
85##
86# check if we have days to count for. If not, we stop the script immediately.
87if( ($DBH->tables('', '', $DAYS_TO_COUNT_TABLE, 'TABLE')) == 0) {
88 if( $QUIET == 0 ) {
89 print "no days to count - bye\n";
90 }
91 $DBH->disconnect();
92 exit;
93}
94
95################################
96# set up results file
97
98##
99# where should we write the output
100if( $QUIET == 0 ) {
101 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;
102 my $dayDate = $mday."_". ($mon+1) ."_". ($year+1900) ."/";
103 $DAY_FOLDER = $IMPORTED_FOLDER . $dayDate;
104
105 ##
106 # Try to create folders if they don't exist
107 mkdir($IMPORTED_FOLDER, 0771) unless (-d $IMPORTED_FOLDER);
108 mkdir($DAY_FOLDER, 0771) unless (-d $DAY_FOLDER);
109
110 my $filename = $DAY_FOLDER. "counter_log\.txt"; # the filename with some kind of timestamp
111 sysopen (RES, $filename, O_CREAT |O_WRONLY, 0755) or die("Can't open result file '$filename': $!");
112}
113
114##
115# PREPARE SQLS
116
117##
118# ANTS
119my $COUNT_ANTS_DATA_DAILY = $DBH->prepare(qq{ SELECT ant, COUNT(id) AS data_count FROM $TABLE_DATA WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY ant })
120 or die("Could not prepare statement to select $TABLE_ANT counts in $TABLE_DATA: " . $DBH->errstr);
121
122my $COUNT_ANTS_DIR_DAILY = $DBH->prepare(qq{ SELECT ant, LEFT(ant, 2) as box, COUNT(id) as dir_count FROM $TABLE_DATA WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) AND i != 1 GROUP BY ant })
123 or die("Could not prepare statement to select $TABLE_ANT counts in $TABLE_DIR: " . $DBH->errstr);
124
125my $COUNT_ANTS_RES_DAILY = $DBH->prepare(qq{ SELECT ant, i, COUNT(id) as res_count FROM $TABLE_DATA WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) AND i IN(3,4) GROUP BY ant })
126 or die("Could not prepare statement to select $TABLE_ANT in $TABLE_DATA: " . $DBH->errstr);
127
128##
129# INSERT DAILY ANTENNAS COUNT
130my $DAILY_ANTS = $DBH->prepare(qq{ INSERT INTO $TABLE_ANTS_COUNT (day, id, data_count, dir_count, res_count) VALUES ( ?,?,?,?,? ) })
131 or die("Could not prepare statement to insert or update daily data in $TABLE_ANTS_COUNT: " . $DBH->errstr);
132
133
134##
135# BOXES
136my $COUNT_BOXES_DATA_DAILY = $DBH->prepare(qq{ SELECT LEFT(ant,2) AS box, COUNT(id) AS data_count FROM $TABLE_DATA WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY box })
137 or die("Could not prepare statement to select $TABLE_BOX counts in $TABLE_DATA: " . $DBH->errstr);
138
139my $COUNT_BOXES_DIR_DAILY = $DBH->prepare(qq{ SELECT box, COUNT(id) as dir_count FROM $TABLE_DIR WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY box })
140 or die("Could not prepare statement to select $TABLE_BOX counts in $TABLE_DIR: " . $DBH->errstr);
141
142my $COUNT_BOXES_RES_DAILY = $DBH->prepare(qq{ SELECT box, COUNT(id) as res_count FROM $TABLE_RES WHERE `box_out` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY box })
143 or die("Could not prepare statement to select $TABLE_BOX in $TABLE_RES: " . $DBH->errstr);
144
145##
146# INSERT DAILY BOXES COUNT
147my $DAILY_BOXES = $DBH->prepare(qq{ INSERT INTO $TABLE_BOXES_COUNT (day, id, data_count, dir_count, res_count) VALUES ( ?,?,?,?,? ) })
148 or die("Could not prepare statement to insert or update daily data in $TABLE_BOXES_COUNT: " . $DBH->errstr);
149
150
151##
152# RFIDS
153my $COUNT_RFIDS_DATA_DAILY = $DBH->prepare(qq{ SELECT rfid, COUNT(id) AS data_count FROM $TABLE_DATA WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY rfid })
154 or die("Could not prepare statement to select $TABLE_RFIDS counts in $TABLE_DATA: " . $DBH->errstr);
155
156my $COUNT_RFIDS_DIR_DAILY = $DBH->prepare(qq{ SELECT rfid, COUNT(id) as dir_count FROM $TABLE_DIR WHERE `time` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY rfid })
157 or die("Could not prepare statement to select $TABLE_RFIDS counts in $TABLE_DIR: " . $DBH->errstr);
158
159my $COUNT_RFIDS_RES_DAILY = $DBH->prepare(qq{ SELECT rfid, i, COUNT(id) as res_count FROM $TABLE_RES WHERE `box_out` BETWEEN ? AND (SELECT DATE_ADD( ? , INTERVAL 1 DAY)) GROUP BY rfid })
160 or die("Could not prepare statement to select $TABLE_RFIDS in $TABLE_RES: " . $DBH->errstr);
161
162
163##
164# INSERT DAILY RFIDS COUNT
165my $DAILY_RFIDS = $DBH->prepare(qq{ INSERT INTO $TABLE_RFIDS_COUNT (day, id, data_count, dir_count, res_count) VALUES ( ?,?,?,?,? ) })
166 or die("Could not prepare statement to insert or update daily data in $TABLE_RFIDS_COUNT: " . $DBH->errstr);
167
168
169##############################################################################
170# MAIN
171##############################################################################
172
173##
174# Getting the days we have to count.
175#
176
177if( $ALL_DAYS == 1 ) {
178 # If the script is started with the --all argument we count for all days in the database.
179 @DAYS_TO_COUNT = @{$DBH->selectcol_arrayref("SELECT DISTINCT DATE(`time`) FROM $TABLE_DATA ORDER BY `time` ASC")}
180 or die("Could not execute statement to get days in $TABLE_DATA: " . $DBH->errstr);
181} else {
182 # If the script is started with no argument we count for the days in the $DAYS_TO_COUNT_TABLE.
183 @DAYS_TO_COUNT = @{$DBH->selectcol_arrayref("SELECT day FROM $DAYS_TO_COUNT_TABLE")}
184 or die("Could not execute statement to get days in $DAYS_TO_COUNT_TABLE: " . $DBH->errstr);
185}
186
187if( $QUIET == 0 ) {
188 my $feedback = "\n................................................\n";
189 $feedback .= "Counting for the following dates:\n\n\t" . join("\n\t",@DAYS_TO_COUNT) . "\n";
190 $feedback .= "\n\t[TOTAL " . scalar(@DAYS_TO_COUNT) . " days]";
191 $feedback .= "\n................................................\n\n";
192
193 print $feedback;
194 printf RES $feedback;
195
196}
197
198##
199# MAIN LOOP OVER DAYS
200my $days_counted = 1;
201my $days_to_count = @DAYS_TO_COUNT;
202my $DAY_START_TIME;
203
204foreach my $DAY (@DAYS_TO_COUNT) {
205
206 ##
207 # This is a bit of a hack to avoid the use of the DATE() function in the SQL-statements.
208 # The use of the function prevents MySQL from using the indices which makes the queries much slower.
209 # (Performance is almost 10 times better)
210 $DAY_START_TIME = "$DAY 00:00:00";
211
212 if( $QUIET == 0 ) {
213 print "[ day $days_counted of $days_to_count ] $DAY ----------------------\n";
214 printf RES "[ day $days_counted of $days_to_count ] $DAY ----------------------\n";
215 }
216
217 ##
218 # Counting boxes data
219 $DBH->do("DELETE FROM $TABLE_BOXES_COUNT WHERE day='$DAY'")
220 or die("Could not execute statement to clear data from $TABLE_BOXES_COUNT for day $DAY: " . $DBH->errstr);
221
222 my $box_data = &CountBoxes($DAY_START_TIME);
223 my $boxes_this_day = &insertDailyCount($box_data, $DAY, $DAILY_BOXES);
224
225 if( $QUIET == 0 ) {
226 print "[OK] boxes: $boxes_this_day\n";
227 printf RES "[OK] boxes: $boxes_this_day\n";
228 }
229
230 ##
231 # Counting antenna data
232 $DBH->do("DELETE FROM $TABLE_ANTS_COUNT WHERE day='$DAY'")
233 or die("Could not execute statement clear data from $TABLE_ANTS_COUNT for day $DAY: " . $DBH->errstr);
234
235 my $ant_data = &CountAnts($DAY_START_TIME);
236 my $ants_this_day = &insertDailyCount($ant_data, $DAY, $DAILY_ANTS);
237
238 if( $QUIET == 0 ) {
239 print "[OK] ants: $ants_this_day\n";
240 printf RES "[OK] ants: $ants_this_day\n";
241 }
242
243 ##
244 # Counting rfid data
245 $DBH->do("DELETE FROM $TABLE_RFIDS_COUNT WHERE day='$DAY'")
246 or die("Could not execute statement to clear data from $TABLE_RFIDS_COUNT for day $DAY: " . $DBH->errstr);
247
248 my $rfid_data = &CountRfids($DAY_START_TIME);
249 my $rfids_this_day = &insertDailyCount($rfid_data, $DAY, $DAILY_RFIDS);
250
251 if( $QUIET == 0 ) {
252 print "[OK] rfids: $rfids_this_day\n";
253 printf RES "[OK] rfids: $rfids_this_day\n";
254 }
255
256
257
258 ##
259 #uncomment to test one day
260 # if($days_counted == 3) {
261 # last;
262 #}
263 if( $QUIET == 0 ) {
264 my $feedback = "------------------------------------------------\n\n";
265 print $feedback;
266 printf RES $feedback;
267
268 }
269 $days_counted++;
270
271}
272
273if( $QUIET == 0 ) {
274 my $feedback = "\n------------------------------------------------\n";
275 $feedback .= "Finishing up\n";
276 $feedback .= "------------------------------------------------\n\n";
277
278 print $feedback;
279 printf RES $feedback;
280
281}
282
283##
284# Updating rfid, box, ant table (data_count, dir_count, res_count, last_data)
285##
286# RFIDS
287if( $QUIET == 0 ) {
288 print "updating table $TABLE_RFIDS ... ";
289 printf RES "updating table $TABLE_RFIDS ... ";
290}
291
292# Getting the rfids summary information
293
294# I had to make two queries due to performance
295my $LASTDATA_RFIDS = $DBH->selectall_hashref(qq{ SELECT rfid, MAX(time) AS last_data FROM $TABLE_DATA GROUP BY rfid }, 'rfid')
296 || die("Could not execute statement to get last_data for rfids from $TABLE_DATA: " . $DBH->errstr);
297
298my $DATA_RFID = $DBH->selectall_hashref(qq{ SELECT DISTINCT id, SUM(data_count) AS data_count, SUM(dir_count) AS dir_count, SUM(res_count) AS res_count FROM $TABLE_RFIDS_COUNT GROUP BY id }, 'id')
299 || die("Could not execute the statement to collect the rfid summary information $TABLE_RFIDS: " . $DBH->errstr);
300
301# Bringing the information together
302foreach my $rfid (keys %$DATA_RFID) {
303 $DATA_RFID->{$rfid}->{'last_data'} = $LASTDATA_RFIDS->{$rfid}->{'last_data'};
304}
305
306my $UPDATE_RFID = $DBH->prepare(qq{ INSERT INTO $TABLE_RFIDS (id, data_count, dir_count, res_count, last) VALUES ( ?,?,?,?,? )
307 ON DUPLICATE KEY UPDATE data_count = ?, dir_count= ?, res_count= ?, last= ?})
308 || die("Could not prepare statement to update the tables in $TABLE_RFIDS: " . $DBH->errstr);
309
310
311my $rfids = &updateTable($DATA_RFID, $UPDATE_RFID);
312if( $QUIET == 0 ) {
313 print "[OK] rfids: $rfids\n";
314 printf RES "[OK] rfids: $rfids\n";
315}
316##
317# ANTENNAS
318if( $QUIET == 0 ) {
319 print "updating table $TABLE_ANT ... ";
320 printf RES "updating table $TABLE_ANT ... ";
321}
322
323# Getting the antennas summary information
324
325# I had to make two queries due to performance
326my $LASTDATA_ANTS = $DBH->selectall_hashref(qq{ SELECT ant, MAX(time) as last_data FROM $TABLE_DATA GROUP BY ant }, 'ant')
327 || die("Could not execute statement to get last_data for ants from $TABLE_DATA: " . $DBH->errstr);
328
329my $DATA_ANT = $DBH->selectall_hashref(qq{ SELECT DISTINCT id, SUM(data_count) AS data_count, SUM(dir_count) AS dir_count, SUM(res_count) AS res_count FROM $TABLE_ANTS_COUNT GROUP BY id }, 'id')
330 || die("Could not execute the statement to collect the rfid summary information $TABLE_RFIDS: " . $DBH->errstr);
331
332# Bringing the information together
333foreach my $ant (keys %$DATA_ANT) {
334 $DATA_ANT->{$ant}->{'last_data'} = $LASTDATA_ANTS->{$ant}->{'last_data'};
335}
336
337
338my $UPDATE_ANT = $DBH->prepare(qq{ INSERT INTO $TABLE_ANT (id, data_count, dir_count, res_count, last) VALUES ( ?,?,?,?,? )
339 ON DUPLICATE KEY UPDATE data_count = ?, dir_count = ?, res_count = ?, last = ?})
340 || die("Could not prepare statement to update the tables in $TABLE_ANT: " . $DBH->errstr);
341
342my $ants = &updateTable($DATA_ANT, $UPDATE_ANT);
343
344if( $QUIET == 0 ) {
345 print "[OK] antennas: $ants\n";
346 printf RES "[OK] antennas: $ants\n";
347}
348##
349# BOXES
350if( $QUIET == 0 ) {
351 print "updating table $TABLE_BOX ... ";
352 printf RES "updating table $TABLE_BOX ... ";
353}
354
355# Getting the boxes summary information
356
357# I had to make two queries due to performance
358my $LASTDATA_BOXES = $DBH->selectall_hashref(qq{ SELECT LEFT(ant,2) AS box, MAX(time) as last_data FROM $TABLE_DATA GROUP BY box }, 'box')
359 || die("Could not execute statement to get last_data for boxes from $TABLE_DATA: " . $DBH->errstr);
360
361my $DATA_BOX = $DBH->selectall_hashref(qq{ SELECT DISTINCT id, SUM(data_count) AS data_count, SUM(dir_count) AS dir_count, SUM(res_count) AS res_count FROM $TABLE_BOXES_COUNT GROUP BY id }, 'id')
362 || die("Could not execute the statement to collect the rfid summary information $TABLE_RFIDS: " . $DBH->errstr);
363
364# Bringing the information together
365foreach my $box (keys %$DATA_BOX) {
366 $DATA_BOX->{$box}->{'last_data'} = $LASTDATA_BOXES->{$box}->{'last_data'};
367}
368
369my $UPDATE_BOX = $DBH->prepare(qq{ INSERT INTO $TABLE_BOX (id, data_count, dir_count, res_count, last) VALUES ( ?,?,?,?,? )
370 ON DUPLICATE KEY UPDATE data_count = ?, dir_count = ?, res_count = ?, last = ?})
371 || die("Could not prepare statement to update $TABLE_BOX: " . $DBH->errstr);
372
373my $boxes = &updateTable($DATA_BOX, $UPDATE_BOX);
374if( $QUIET == 0 ) {
375 print "[OK] boxes: $boxes\n";
376 printf RES "[OK] boxes: $boxes\n";
377}
378
379####################################
380# FINISH
381####################################
382
383##
384# deleting the temporary table $DAYS_TO_COUNT_TABLE
385#$DBH->do(qq{ DROP TABLE IF EXISTS $DAYS_TO_COUNT_TABLE }) || die("Could not drop table $DAYS_TO_COUNT_TABLE " . $DBH->errstr);
386
387if( $QUIET == 0 ) {
388 print"\n================================================\n";
389 print "COUNTER.PL COMPLETE";
390 print"\n================================================\n";
391}
392$DBH->disconnect();
393
394# my @args = ( $SCRIPT_PATH."dbsync.pl");
395# system(@args) == 0
396# or die "system @args failed: $?";
397# exit;
398
399##############################################################################
400# SUBS
401##############################################################################
402
403# COUNT ANTENNA DATA
404##############################################################################
405sub CountAnts {
406
407 my $day = shift;
408
409 if( $QUIET == 0 ) {
410 print "=> calculating antenna count ... ";
411 }
412
413
414 ##
415 # hash for ant counts
416 my $ant_counts = {};
417
418 ##
419 # DATA COUNT ANT
420
421 $COUNT_ANTS_DATA_DAILY->execute($day, $day)
422 or die("Could not execute statement to count $TABLE_ANT in $TABLE_DATA: " . $DBH->errstr);
423
424 my $ants_data_count = $COUNT_ANTS_DATA_DAILY->fetchall_hashref('ant');
425
426 # data count to hash
427 foreach my $ant (keys %$ants_data_count) {
428 my $data_count = $ants_data_count->{$ant}->{'data_count'} || 0;
429 $ant_counts->{$ant}->{'data_count'} = $data_count;
430
431 }
432
433
434 ##
435 # DIR COUNT ANT
436 $COUNT_ANTS_DIR_DAILY->execute($day, $day)
437 or die("Could not execute statement to count $TABLE_ANT in $TABLE_DIR: " . $DBH->errstr);
438
439 my $ants_dir_count = $COUNT_ANTS_DIR_DAILY->fetchall_hashref('ant');
440
441 # dir count to hash
442 foreach my $ant (keys %$ants_dir_count) {
443 my $dir_count = $ants_dir_count->{$ant}->{'dir_count'} || 0;
444 $ant_counts->{$ant}->{'dir_count'} = $dir_count;
445 }
446
447 ##
448 # RES COUNT ANT
449 $COUNT_ANTS_RES_DAILY->execute($day, $day)
450 or die("Could not execute statement to count $TABLE_ANT in $TABLE_RES: " . $DBH->errstr);
451
452 my $ants_res_count = $COUNT_ANTS_RES_DAILY->fetchall_hashref('ant');
453
454 # res count to hash
455 foreach my $ant (keys %$ants_res_count) {
456 my $res_count = $ants_res_count->{$ant}->{'res_count'} || 0;
457 $ant_counts->{$ant}->{'res_count'} = $res_count;
458 }
459
460 return $ant_counts;
461
462}
463
464# COUNT BOX DATA
465##############################################################################
466
467sub CountBoxes {
468
469 my $day = shift;
470
471 if( $QUIET == 0 ) {
472 print "=> calculating box count ... ";
473 }
474
475
476 ##
477 # hash for box counts
478 my $boxes_counts = {};
479
480 ##
481 # DATA COUNT BOX
482 $COUNT_BOXES_DATA_DAILY->execute($day, $day)
483 or die("Could not execute statement to count $TABLE_BOX in $TABLE_DATA: " . $DBH->errstr);
484
485 my $boxes_data_count = $COUNT_BOXES_DATA_DAILY->fetchall_hashref('box');
486
487 # data count to hash
488 foreach my $box (keys %$boxes_data_count) {
489 my $data_count = $boxes_data_count->{$box}->{'data_count'} || 0;
490 $boxes_counts->{$box}->{'data_count'} = $data_count;
491 }
492
493
494 ##
495 # DIR COUNT BOX
496 $COUNT_BOXES_DIR_DAILY->execute($day, $day)
497 or die("Could not execute statement to count $TABLE_BOX in $TABLE_DIR: " . $DBH->errstr);
498
499 my $boxes_dir_count = $COUNT_BOXES_DIR_DAILY->fetchall_hashref('box');
500
501 # dir count to hash
502 foreach my $box (keys %$boxes_dir_count) {
503 my $dir_count = $boxes_dir_count->{$box}->{'dir_count'} || 0;
504 $boxes_counts->{$box}->{'dir_count'} = $dir_count;
505 }
506
507 ##
508 # RES COUNT BOX
509 $COUNT_BOXES_RES_DAILY->execute($day, $day)
510 or die("Could not execute statement to count $TABLE_BOX in $TABLE_RES: " . $DBH->errstr);
511
512 my $boxes_res_count = $COUNT_BOXES_RES_DAILY->fetchall_hashref('box');
513
514 # res count to hash
515 foreach my $box (keys %$boxes_res_count) {
516
517 my $res_count = $boxes_res_count->{$box}->{'res_count'} || 0;
518 $boxes_counts->{$box}->{'res_count'} = $res_count;
519 }
520
521 return $boxes_counts;
522
523}
524
525
526
527# COUNT RFID DATA
528##############################################################################
529
530sub CountRfids {
531
532 my $day = shift;
533
534 if( $QUIET == 0 ) {
535 print "=> calculating rfid count ... ";
536 }
537
538
539 ##
540 # hash for rfid counts
541 my $rfids_counts = {};
542
543 ##
544 # DATA COUNT RFID
545 $COUNT_RFIDS_DATA_DAILY->execute($day, $day)
546 or die("Could not execute statement to count $TABLE_RFIDS in $TABLE_DATA: " . $DBH->errstr);
547
548 my $rfids_data_count = $COUNT_RFIDS_DATA_DAILY->fetchall_hashref('rfid');
549
550 # data count to hash
551 foreach my $rfid (keys %$rfids_data_count) {
552 my $data_count = $rfids_data_count->{$rfid}->{'data_count'} || 0;
553 $rfids_counts->{$rfid}->{'data_count'} = $data_count;
554 }
555
556
557 ##
558 # DIR COUNT RFID
559 $COUNT_RFIDS_DIR_DAILY->execute($day, $day)
560 or die("Could not execute statement to count $TABLE_RFIDS in $TABLE_DIR: " . $DBH->errstr);
561
562 my $rfids_dir_count = $COUNT_RFIDS_DIR_DAILY->fetchall_hashref('rfid');
563
564 # dir count to hash
565 foreach my $rfid (keys %$rfids_dir_count) {
566 my $dir_count = $rfids_dir_count->{$rfid}->{'dir_count'} || 0;
567 $rfids_counts->{$rfid}->{'dir_count'} = $dir_count;
568 }
569
570 ##
571 # RES COUNT RFID
572 $COUNT_RFIDS_RES_DAILY->execute($day, $day)
573 or die("Could not execute statement to count $TABLE_RFIDS in $TABLE_RES: " . $DBH->errstr);
574
575 my $rfids_res_count = $COUNT_RFIDS_RES_DAILY->fetchall_hashref('rfid');
576
577 # res count to hash
578 foreach my $rfid (keys %$rfids_res_count) {
579
580 my $res_count = $rfids_res_count->{$rfid}->{'res_count'} || 0;
581 $rfids_counts->{$rfid}->{'res_count'} = $res_count;
582 }
583
584 return $rfids_counts;
585
586}
587
588
589##
590# insert daily data count into the db
591sub insertDailyCount {
592
593 my ($data, $day, $sth) = @_;
594
595 foreach my $item (keys %$data) {
596 my $data_count = $data->{$item}->{'data_count'} ||0;
597 my $dir_count = $data->{$item}->{'dir_count'} ||0;
598 my $res_count = $data->{$item}->{'res_count'} || 0;
599
600
601 $sth->execute($day, $item, $data_count , $dir_count, $res_count)
602 or die("Could not execute statement to insert daily data into data count table: " . $DBH->errstr);
603
604 }
605
606 return keys %$data;
607
608}
609
610##
611# update main tables for rfid boxes antennas
612sub updateTable {
613
614 my ($data,$sth) = @_;
615
616 foreach my $id (keys %$data) {
617
618 my $data_count = $data->{$id}->{'data_count'} || 0;
619 my $dir_count = $data->{$id}->{'dir_count'} || 0;
620 my $res_count = $data->{$id}->{'res_count'} || 0;
621 my $last = $data->{$id}->{'last_data'} || '0000-00-00 00:00:00';
622
623 $sth->bind_param(1, $id);
624 $sth->bind_param(2, $data_count, SQL_INTEGER);
625 $sth->bind_param(3, $dir_count, SQL_INTEGER);
626 $sth->bind_param(4, $res_count, SQL_INTEGER);
627 $sth->bind_param(5, $last,SQL_DATETIME);
628 $sth->bind_param(6, $data_count, SQL_INTEGER);
629 $sth->bind_param(7, $dir_count, SQL_INTEGER);
630 $sth->bind_param(8, $res_count, SQL_INTEGER);
631 $sth->bind_param(9, $last, SQL_DATETIME);
632
633
634 $sth->execute($id, $data_count, $dir_count, $res_count, $last, $data_count,$dir_count,$res_count, $last)
635 or die("Could not execute statement to insert or update table : " . $DBH->errstr);
636
637 }
638
639 return keys %$data;
640
641}