summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-11-10 12:48:47 +0100
committerYorhel <git@yorhel.nl>2015-11-10 12:48:47 +0100
commitfd9f224ad7e1d6ebe4f7abba75526b5190c963ba (patch)
treee23f4975f5282b49fc685ba0b9125402f0fe10c5 /lib/VNDB/DB
parent339f47375136b8abcb418cbdeae8adeaa2d3e73d (diff)
parent721f22030f2f87c291bde088bf96648e9cdedc0d (diff)
Merge branch 'poll' of https://github.com/morkt/vndb into polls
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/Discussions.pm2
-rw-r--r--lib/VNDB/DB/Polls.pm95
2 files changed, 97 insertions, 0 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 48859611..c380feeb 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -40,6 +40,7 @@ sub dbThreadGet {
my @select = (
qw|t.id t.title t.count t.locked t.hidden|,
$o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
+ 'p.id AS poll',
);
my @join = (
@@ -49,6 +50,7 @@ sub dbThreadGet {
) : (),
$o{type} && $o{iid} ?
'JOIN threads_boards tb ON tb.tid = t.id' : (),
+ 'LEFT JOIN polls p ON p.tid = t.id',
);
my $order = sprintf {
diff --git a/lib/VNDB/DB/Polls.pm b/lib/VNDB/DB/Polls.pm
new file mode 100644
index 00000000..0b8ff81d
--- /dev/null
+++ b/lib/VNDB/DB/Polls.pm
@@ -0,0 +1,95 @@
+
+package VNDB::DB::Polls;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|dbPollGet dbPollVote dbPollAdd dbPollEdit|;
+
+
+# Options: id, tid, uid, what
+# What: votes
+sub dbPollGet {
+ my($self, %o) = @_;
+ $o{what} ||= '';
+ $o{uid} ||= $self->authInfo->{id};
+
+ my %where = (
+ $o{id} ? ('p.id = ?' => $o{id}) :
+ $o{tid} ? ('p.tid = ?' => $o{tid}) : (),
+ );
+
+ my @select = (
+ qw|p.id p.question p.max_options p.preview p.recast|,
+ $o{what} =~ /votes/ ?
+ ('(SELECT COUNT(DISTINCT uid) FROM polls_votes pv WHERE pv.pid = p.id) AS votes') : (),
+ );
+ my $p = $self->dbRow(q|
+ SELECT !s
+ FROM polls p
+ !W|,
+ join(', ', @select), \%where
+ );
+ return $p unless %$p;
+
+ my $options_query = $o{what} =~ /votes/ ?
+ q|SELECT id, option, COUNT(pv.optid) AS votes
+ FROM polls_options po
+ LEFT JOIN polls_votes pv ON po.id = pv.optid
+ WHERE po.pid = ? GROUP BY id ORDER BY id| :
+ q|SELECT id, option
+ FROM polls_options po
+ WHERE po.pid = ? ORDER BY id|;
+ $p->{options} = $self->dbAll($options_query, $p->{id});
+
+ $p->{user} = $o{uid} ? [
+ map $_->{optid}, @{$self->dbAll(q|
+ SELECT optid FROM polls_votes
+ WHERE pid = ? AND uid = ?|, $p->{id}, $o{uid})}
+ ] : [];
+
+ return $p;
+}
+
+
+sub dbPollVote {
+ my($self, $id, %o) = @_;
+
+ $self->dbExec('DELETE FROM polls_votes WHERE pid = ? AND uid = ?', $id, $o{uid});
+ $self->dbExec('INSERT INTO polls_votes (pid, uid, optid) VALUES (?, ?, ?)',
+ $id, $o{uid}, $_) for @{$o{options}};
+}
+
+
+sub dbPollAdd {
+ my($self, %o) = @_;
+
+ my $id = $self->dbRow(q|
+ INSERT INTO polls (tid, question, max_options, preview, recast)
+ VALUES (?, ?, ?, ?, ?) RETURNING id|,
+ $o{tid}, $o{question}, $o{max_options}, $o{preview}, $o{recast}
+ )->{id};
+
+ $self->dbExec('INSERT INTO polls_options (pid, option) VALUES (?, ?)', $id, $_)
+ for @{$o{options}};
+
+ return $id;
+}
+
+
+sub dbPollEdit {
+ my($self, $id, %o) = @_;
+
+ my %set = map exists $o{$_} ? ("$_ = ?" => $o{$_}) : (),
+ qw|question max_options preview recast|;
+
+ $self->dbExec('UPDATE polls !H WHERE id = ?', \%set, $id);
+ $self->dbExec('DELETE FROM polls_options WHERE pid = ?', $id);
+ $self->dbExec('INSERT INTO polls_options (pid, option) VALUES (?, ?)', $id, $_)
+ for @{$o{options}};
+}
+
+
+1;
+