commit 89ba1c136a71c97e18039ab867191edb55b55e10
from: Izzy Blacklock <izzyb@planetofnix.com>
date: Thu May 18 21:24:51 2023 UTC

Added list of ip addresses user by user in user report
Also added $self->{dbh} object

commit - 917188509192877feb9d6024b7e242810142d665
commit + 89ba1c136a71c97e18039ab867191edb55b55e10
blob - 4ca243f3159767e56176d2f1887bb08d1697892f
blob + 5ef4ee34fad34cd47d99bdf6ea0ea288c9fc4f05
--- lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
+++ lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
@@ -9,6 +9,8 @@ use DBI;
 use DBD::SQLite;
 use DateTime;
 
+
+use Data::Dumper;
 # setup log level constents
 use constant {
     NONE => 0,
@@ -32,6 +34,7 @@ sub connectDB {
 		AutoCommit       => 1,
 		FetchHashKeyName => 'NAME_lc',
 	}) or die "Couldn't connect to database: " . $DBI::errstr;
+	$self->{dbh} = $dbh;
 	if (!(-s "$dbpath")) {
 		main::debug(ALL, "Cant locate $dbpath");
 		exit 1;
@@ -46,6 +49,7 @@ sub connectDB {
 sub disconnectDB {
 	my $self=shift;
 	my $dbh = shift;
+	delete $self->{dbh};
 	$dbh->disconnect();
 }
 
@@ -86,7 +90,6 @@ 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
@@ -97,9 +100,14 @@ sub getUser {
 	# 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";
-		$pm .= $seen->{what} . ".";
+		$pm .= "\nLast Seen: "; 
+		my $dt = DateTime->from_epoch($seen->{time});
+		$pm .= $dt->strftime('%Y-%m-%d %H:%M:%S') 
+			. " in " . $seen->{channel} . "\n"
+			. $seen->{what} . ".\n";
 	}
+	$pm .= $self->getIP4user($user);
+	# Get list of IP Addresses
 	$self->disconnectDB($dbh);
 	$self->say({who=>$msg->{who},channel=>'msg',body=>$pm});
 	if ($msg->{channel} ne 'msg') { # Notify we are replying in PM.
@@ -119,7 +127,6 @@ sub getEmail {
 	$self->disconnectDB($dbh);
 	$self->say({who=>$msg->{who}, channel=>'msg',body=>$pm});
 }
-use Data::Dumper;
 
 sub getIP {
 	my $self = shift;
@@ -137,7 +144,6 @@ sub getIP {
 			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";
 			}
 		}
@@ -147,6 +153,30 @@ sub getIP {
 	return 1;
 }
 
+sub getIP4user {
+	my $self = shift;
+	my $user = shift;
+	my $dbh = $self->{dbh};
+	my $stmts = [
+		qq{
+			SELECT irc.localtime,ip FROM bnc JOIN irc ON (bnc.ircid = irc.id) 
+			WHERE username IS ? ORDER BY irc.localtime DESC
+		},
+		qq{SELECT max(epoch),address FROM znclog WHERE user = ? GROUP BY address order by epoch DESC},
+	];
+	my $pm = "";
+	for my $stmt (@$stmts) {
+		for my $rows ($dbh->selectall_arrayref($stmt, undef, $user)) {
+			for my $row (@$rows) {
+				my $dt = DateTime->from_epoch(epoch => $row->[0]);
+				$pm .= $dt->strftime('%Y-%m-%d %H:%M:%S') . " -- " . $row->[1] . "\n";
+			}
+		}
+	}
+	return $pm;
+}
+
+
 #        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()";