use DBI;
use Net::SFTP::Foreign;
use Data::Dumper qw(Dumper);

my $dbh = DBI->connect('dbi:mysql:database=realtime;host=localhost','root','xxxxxxx');
my $sql = "select id,codename,full_name from stations";
my $sth = $dbh->prepare($sql);
$sth->execute();
my $rs = $sth->fetchrow_hashref();
$rs    = $sth->fetchall_arrayref( { id        => 1,
                                    full_name => 1,
                                    codename  => 1 } );

my ($sc,$mn,$hr,$md,$mo,$yr,$wd,$yd,$id) = localtime(time); $yr += 1900;

my $hostname     = 'xxx.xxx.xxx.xxx';
my $username     = 'xxxxx';
my $Dbase_search = '/data/archive/real-time/staging/radial';
my @latestfiles;

print "\nOPENING SFTP connection to $hostname as user $username ...\n\n";
my $sftp = Net::SFTP::Foreign->new( $hostname , user=>$username );

foreach my $station ( @{$rs} ) {

    my $Dsearch = $Dbase_search.'/'.$station->{codename}.'/'.$yr;
    print "Searching $Dsearch for files modified in the last 24 hours ... \n";
    my @files = $sftp->find( $Dsearch , wanted => sub{
                                 use Fcntl qw(S_ISREG);
                                 my $now            = time();
                                 my $yesterday      = $now-(24*3600);
                                 my (undef, $entry) = @_;
                                 ( ($entry->{a}->mtime < $now) and ($entry->{a}->mtime > $yesterday) and S_ISREG($entry->{a}->perm) )
                             } );

    push @latestfiles, $_->{filename} for $files[-1];
    print "Latest $station->{full_name} file: $_->{filename}\n" for $files[-1];

}

print "\nCLOSING SFTP connection\n\n";
$sftp->disconnect;