get.pm

package commands::get;

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

use strict;
use warnings;

our $dbh;
our $irc;

sub exec {
	my ($kernel, $user, $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;

    if (@tags) {
        my $params = join ', ' => ('?') x @tags;
        my $sth;

        if ($all) {
            $sth = $dbh->prepare('select id, sender, title, url
                from playbot
                natural join playbot_tags
                where tag in ('.$params.')
                group by id
                having count(*) >= ?
                order by rand()
                limit 1');
            $sth->execute(@tags, scalar @tags);
        }
        else {
            $sth = $dbh->prepare('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.')
                and pc.chan = ?
                group by p.id
                having count(*) >= ?
                order by rand()
                limit 1');
            $sth->execute(@tags, $chan->[0], scalar @tags);
        }

        $content = $sth->fetch;

        if (!$content) {
            $irc->yield(privmsg => $chan => "Je n'ai rien dans ce registre.");
            return
        }
    }
    else {
        my $sth;

        if ($all) {
            $sth = $dbh->prepare('select id, sender, title, url from playbot
                order by rand()
                limit 1');
            $sth->execute;
        }
        else {
            $sth = $dbh->prepare('select p.id, p.sender, p.title, p.url
                from playbot p