#!/opt/perl/bin/perl # ListHits.pl # Version 1.1 # 31 May 2006 # # By: Justin Mercier # IQ AFIS, Baghdad Iraq # This script will query the ADS for all searches that resultes in hits. # The file is exported to a CSV file. # # Credits: Many thanks to Akiko Baldridge and John Sicklick for # lending me their script-writing ninja nunchaku. # THIS SCRIPT IS NEITHER PROVIDED NOR SUPPORTED BY MOTOROLA! # Declare the trim function defined at the bottom which is used # to remove leading and trailing whitespace from strings. sub trim($); # Check to make sure that we are the oracle user. $user = `whoami`; chomp ($user); if ($user ne "oracle") { print "You are logged in as $user. You must be logged in as oracle.\n"; exit 1 } print "Querying ADS for hits...\n"; # Create and array called @hits and populate it with the following # fields from Oracle ads/printrak: # # Search TCN: ExtractTagValue(afis_search.search_info,'display-id') # Respondent: ExtractTagValue(afis_search_respondent.resp_info,'display-id') # Search Date: afis_search.search_date # Review Date: afis_search_respondent.review_date # Searched By: afis_search.operator # Reviewed By: afis_search.review_operator # AFIS Score: afis_search_respondent.score # # This is done by querying for the above fields with the same SEARCH_ID # and a REVIEW_HIT of 1. @hits = qx{sqlplus -s ads/printrak << EOF set feedback off set pagesize 0 set linesize 1000 set heading off set feedback off set colsep ',' set timing off column SEARCH format a50 column RESPONDENT format a50 select ExtractTagValue(search_info,'display-id') as SEARCH, ExtractTagValue(resp_info,'display-id') as RESPONDENT, a_s.search_date, a_s_r.review_date, a_s.operator, a_s.review_operator, a_s_r.score from afis_search a_s,afis_search_respondent a_s_r where a_s.search_id = a_s_r.search_id and a_s_r.review_hit = 1; exit EOF }; # Now we need to grab each line from the array and and parse it # into a multi-dimensional array @hit_details, with the following # structure: # # @hit_details: # [0] %Record 0: {search, respondent, search_date, review_date, # operator, review_operator, score} # [1] %Record 1: {search, respondent, search_date, review_date, # operator, review_operator, score} # [2] %Record 2: {search, respondent, search_date, review_date, # operator, review_operator, score} # # ... and so on. Why use multi-dimensional arrays? Because they # are too cool for school. Also note the use of trim on each field. for ($i = 0; $i < scalar @hits; $i++) { @t = split /,/, $hits[$i]; $hit_details[$i] = ( {search => trim($t[0]), respondent => trim($t[1]), search_date => trim($t[2]), review_date => trim($t[3]), operator => trim($t[4]), review_operator => trim($t[5]), score => trim($t[6])} ); } # Ok, now we set up to write to our CSV, and if necessary, move any # pre-existing file out of the way. $csvfile = "/var/tmp/xenahits.csv"; if (-e $csvfile) { rename("$csvfile", "$csvfile.bak"); }; # Now we open our file handle and write a header line. open OUT, "> $csvfile" or die "Could not write CSV: $! \n"; print OUT "Search Number,Search TCN,Respondent,Search Date,Review Date,Submitted By,Reviewed By,AFIS Score\n"; $counter = 0; #create a counter # Walk the multi-dimensional array hashes and write to the CSV for ($i = 0; $i < scalar @hit_details; $i++) { print OUT "$counter,"; print OUT "$hit_details[$i]{search},"; print OUT "$hit_details[$i]{respondent},"; print OUT "$hit_details[$i]{search_date},"; print OUT "$hit_details[$i]{review_date},"; print OUT "$hit_details[$i]{operator},"; print OUT "$hit_details[$i]{review_operator},"; print OUT "$hit_details[$i]{score}\n"; $counter++; # increment the counter } close OUT; # close the filehandle print "...done. File written to $csvfile.\n"; ###### Subroutines sub trim($) { my $string = shift; $string =~ s/^\s+//; $string =~ s/\s+$//; return $string; }