commit 917188509192877feb9d6024b7e242810142d665
from: Izzy Blacklock <izzyb@planetofnix.com>
date: Thu May 18 20:24:34 2023 UTC

Added ability to search for ip

Added update to help message

commit - b2b8298e78436b45bd4e6e53c748746f99cca3e5
commit + 917188509192877feb9d6024b7e242810142d665
blob - a596a0dcc4888321007d0101794ea518d3d730c2
blob + 4ca243f3159767e56176d2f1887bb08d1697892f
--- lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
+++ lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
@@ -7,6 +7,7 @@ use base qw(Bot::BasicBot::Pluggable::Module);
 # Database modules for botnow.db
 use DBI;
 use DBD::SQLite;
+use DateTime;
 
 # setup log level constents
 use constant {
@@ -85,6 +86,15 @@ sub getUser {
 	my $stmt = qq{select username,email from bnc where username is ?};
 	my @userDetails = $dbh->selectrow_array($stmt, undef, $user);
 	my $pm = $userDetails[0] . ": " . $userDetails[1];
+	
+	# Check last time connected to znc
+	my $zncSeen = $dbh->selectrow_arrayref(qq{
+		SELECT * FROM zncLog WHERE user=? ORDER BY epoch DESC LIMIT 1
+	}, undef, $user);
+	if (defined $zncSeen) {
+		$pm .= "\nZNC Last Connected: " . $zncSeen->[2];
+	}
+	# Check last time bot seen user
 	my $seen = $self->bot->module('seen')->get("seen_$user");
 	if (defined $seen) {
 		$pm .= "\nLast Seen: " . $seen->{time} . " in " . $seen->{channel} . "\n";
@@ -108,17 +118,46 @@ sub getEmail {
 	my $pm = join (", ", @$users);
 	$self->disconnectDB($dbh);
 	$self->say({who=>$msg->{who}, channel=>'msg',body=>$pm});
+}
+use Data::Dumper;
 
+sub getIP {
+	my $self = shift;
+	my $msg=shift;
+	my $ip = shift;
+	my $dbh = $self->connectDB();
+	my $stmts = [
+		qq{SELECT irc.localtime,username FROM bnc JOIN irc ON (bnc.ircid = irc.id) WHERE ip IS ?},
+		qq{SELECT max(epoch),user FROM znclog WHERE address = ? GROUP BY user},
+	];
+	my @users;
+	my $pm = "";
+	for my $stmt (@$stmts) {
+		for my $rows ($dbh->selectall_arrayref($stmt, undef, $ip)) {
+			for my $row (@$rows) {
+#				push @users, $row;
+				my $dt = DateTime->from_epoch(epoch => $row->[0]);
+				
+				$pm .= $dt->strftime('%Y-%m-%d %H:%M:%S') . " -- " . $row->[1] . "\n";
+			}
+		}
+	}
+	$self->disconnectDB($dbh);
+	$self->say({who=>$msg->{who}, channel=>'msg', body=>$pm});
+	return 1;
 }
+
 #        my $stmt=qq{select * from bnc join irc on (bnc.ircid = irc.id) where username is ?};
 #        my $sth=$dbh->prepare($stmt);
 #        $sth->execute($username) or die "execution failed: $dbh->errstr()"; 
 #        while (my $row=$sth->fetchrow_hashref) {
 
-
-
 sub help {
-  return "botnowdb: Database interface for botnow.db";
+  return qq{botnowdb: Database interface for botnow.db
+  botnow User <username>
+  botnow email <email address>
+  botnow ip <IP Address>
+  };
 }
 
 sub told {
@@ -137,6 +176,7 @@ sub told {
 			users  => sub { return $self->listUsers( @_ ) },
 			user   => sub { return $self->getUser( @_ ) },
 			email => sub { return $self->getEmail( @_ ) },
+			ip => sub { return $self->getIP( @_ ) },
 		);
 		if (!defined($actions{$cmds[0]})) {
 			return $self->help();