get.pm

package commands::get;

require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw(exec);

use strict;
use warnings;
use Scalar::Util qw(looks_like_number);

our $dbh;
our $irc;
our $log;

sub exec {
	my ($kernel, $nick, $chan, $msg) = @_;

    # if we are in a query or arg -all, we search in all the channels
    my $all = 0;
    $all = 1 if ($chan->[0] !~ /^#/ || $msg =~ s/-all//);

    my @tags = ($msg =~ /#([a-zA-Z0-9_-]+)/g);
    my $content;
    my $req;
    my $sth;

    my @words;
    while ($msg =~ /(^| )([a-zA-Z0-9_-]+)/g) {
        unshift @words, '%'.$2.'%';
    }

    my $words_sql;
    foreach (@words) {
        $words_sql .= ' and ' if ($words_sql);
        $words_sql .= "concat(sender, ' ', title) like ?";
    }

    if (@tags) {
        if (looks_like_number($tags[0])) {
            $sth = $dbh->prepare('select id, sender, title, url
                from playbot
                where id = ?');
            $sth->execute($tags[0]);
        }
        else {
            my $params = join ', ' => ('?') x @tags;

            if ($all) {
                $req = 'select id, sender, title, url
                    from playbot
                    natural join playbot_tags
                    where tag in ('.$params.')';
                $req .= ' and '.$words_sql if ($words_sql);
                $req .= ' group by id
                    having count(*) >= ?
                    order by rand()
                    limit 1';

                $sth = $dbh->prepare($req);
                $sth->execute(@tags, @words, scalar @tags);
            }
            else {
                $req = 'select p.id, p.sender, p.title, p.url
                    from playbot p
                    natural join playbot_tags pt
                    join playbot_chan pc on p.id = pc.content
                    where pt.tag in ('.$params.')';
                $req .= ' and '.$words_sql if ($words_sql);
                $req .= ' and pc.chan = ?
                    group by p.id