get.pm

package commands::get;

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 = ($msg =~ /(?:^| )([a-zA-Z0-9_-]+)/g);
    my @words_param;
    while ($msg =~ /(?:^| )([a-zA-Z0-9_-]+)/g) {
        unshift @words_param, '%'.$1.'%';
    }

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

    if (@words && looks_like_number($words[0])) {
        $sth = $dbh->prepare('select id, sender, title, url
            from playbot
            where id = ?');
        $sth->execute($words[0]);

        $content = $sth->fetch;

        if (!$content) {
            $irc->yield(privmsg => $chan => "Je n'ai rien dans ce registre.");
            return
        }
    }
    elsif (@tags) {
        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_param, 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.')';