#!/usr/bin/perl -w
# urls.pl - query logged URLs from an SQL database
# author: Frank "yogan" Blendinger <fb@intoxicatedmind.net>

use strict;
use CGI;
use DBI;
use Getopt::Std;

our($opt_h, $opt_N, $opt_C, $opt_U, $opt_l, $opt_n, $opt_c, $opt_u, $opt_d);
getopts("hNCUln:c:u:d:");

if ($opt_h) {
    print "Usage: urls.pl OPTIONS\n";
    print "   -d DAYS      last DAYS days (dafault is 7)\n";
    print "   -n NICK      only URLs posted by nicknames like NICK\n";
    print "   -c CHANNEL   only URLs posted in channels like CHANNEL\n";
    print "   -u URL       only URLs like URL\n";
    print "   -N           group by nick\n";
    print "   -C           group by channel\n";
    print "   -U           group by URL\n";
    print "   -l           print whole line (default is only URL)\n";
    exit 0;
}

my $filter;
if ($opt_d) {
    $filter = "DATE_SUB(CURDATE(),INTERVAL $opt_d DAY) <= inserttime";
} else {
    $filter = "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= inserttime";
}
$opt_n and $filter .= " AND nick LIKE \"%".$opt_n."%\"";
$opt_c and $filter .= " AND target LIKE \"%".$opt_c."%\"";
$opt_u and $filter .= " AND url LIKE \"%".$opt_u."%\"";

my $group = "";
if ($opt_N || $opt_C || $opt_U) {
    $group = "GROUP BY ";
    if ($opt_N) { $group .= "nick" unless ($opt_C || $opt_U) ; }
    if ($opt_C) { $group .= "target" unless ($opt_N || $opt_U) ; }
    if ($opt_U) { $group .= "url" unless ($opt_N || $opt_C) ; }
}

# DB access data
my $dbs = 'dbi:mysql:yourdb';
my $dbuser = 'username';
my $dbpw = '*****';

my $cgi_obj = new CGI;

# Connect to the database.
my $dbh = DBI->connect($dbs, $dbuser, $dbpw) ||
    die "Can't connect to database: $DBI::errstr\n";

# Prepare query statement.
my $query;
my $sth;
if ($group) {
    my $col;
    $opt_N and $col = "nick";
    $opt_C and $col = "target";
    $opt_U and $col = "url";
    $query = "SELECT COUNT(*), $col FROM urlevent WHERE $filter $group ORDER BY COUNT(*) DESC ;";
    $sth = $dbh->prepare( $query ) || die "Can't prepare statement: $DBI::errstr\n";
} else {
    $query = "SELECT inserttime, nick, target, line, url FROM urlevent WHERE $filter ;";
    $sth = $dbh->prepare( $query ) || die "Can't prepare statement: $DBI::errstr\n";
}

#print $query . "\n" and exit;

# Execute query.
$sth->execute() ||
    die "Can't execute query: $DBI::errstr\n";

# Handle results.
if ($group) {
    while ( my @result = $sth->fetchrow_array() ){
        print $result[0] . " | " . $result[1] . "\n";
    }
} else {
    while ( my @result = $sth->fetchrow_array() ){
        my $url_or_line;
        if ($opt_l) {
            $url_or_line = $result[3];
        } else {
            $url_or_line = $result[4];
        }
        print $result[0] . " | " . $result[1] . " | " . $result[2] . " | "
            . $url_or_line . "\n";
    }
}

# Free statement handle resources.
$sth->finish();

# Close database connection.
$dbh->disconnect;