1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
package VNDB::DB::ULists;
use strict;
use warnings;
use Exporter 'import';
our @EXPORT = qw|
dbRListGet dbRListAdd dbRListDel
dbVoteStats
|;
# Options: uid rid
sub dbRListGet {
my($self, %o) = @_;
my %where = (
'uid = ?' => $o{uid},
$o{rid} ? ('rid IN(!l)' => [ ref $o{rid} ? $o{rid} : [$o{rid}] ]) : (),
);
return $self->dbAll(q|
SELECT uid, rid, status
FROM rlists
!W|,
\%where
);
}
# Arguments: uid rid status
# rid can be an arrayref only when the rows are already present, in which case an update is done
sub dbRListAdd {
my($self, $uid, $rid, $stat) = @_;
$self->dbExec(
'UPDATE rlists SET status = ? WHERE uid = ? AND rid IN(!l)',
$stat, $uid, ref($rid) ? $rid : [ $rid ]
)
||
$self->dbExec(
'INSERT INTO rlists (uid, rid, status) VALUES(?, ?, ?)',
$uid, $rid, $stat
);
}
# Arguments: uid, rid
sub dbRListDel {
my($self, $uid, $rid) = @_;
$self->dbExec(
'DELETE FROM rlists WHERE uid = ? AND rid IN(!l)',
$uid, ref($rid) ? $rid : [ $rid ]
);
}
# Arguments: 'vid', id
# Returns an arrayref with 10 elements containing the [ count(vote), sum(vote) ]
# for votes in the range of ($index+0.5) .. ($index+1.4)
sub dbVoteStats {
my($self, $col, $id, $ign) = @_;
my $r = [ map [0,0], 0..9 ];
$r->[$_->{idx}] = [ $_->{votes}, $_->{total} ] for (@{$self->dbAll(q|
SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
FROM ulist_vns uv
WHERE uv.vote IS NOT NULL AND NOT EXISTS(SELECT 1 FROM users u WHERE u.id = uv.uid AND u.ign_votes)
AND uv.vid = ?
GROUP BY (vote::numeric/10)::int|,
$id
)});
return $r;
}
1;
|