summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/ULists.pm
blob: 4c1d10ae6ccf0cde001d12a00a1477196895f696 (plain)
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;