commit - 917188509192877feb9d6024b7e242810142d665
commit + 89ba1c136a71c97e18039ab867191edb55b55e10
blob - 4ca243f3159767e56176d2f1887bb08d1697892f
blob + 5ef4ee34fad34cd47d99bdf6ea0ea288c9fc4f05
--- lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
+++ lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
use DBD::SQLite;
use DateTime;
+
+use Data::Dumper;
# setup log level constents
use constant {
NONE => 0,
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;
sub disconnectDB {
my $self=shift;
my $dbh = shift;
+ delete $self->{dbh};
$dbh->disconnect();
}
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
# 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.
$self->disconnectDB($dbh);
$self->say({who=>$msg->{who}, channel=>'msg',body=>$pm});
}
-use Data::Dumper;
sub getIP {
my $self = shift;
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";
}
}
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()";