diff options
Diffstat (limited to 'lib/VNDB/DB/Users.pm')
-rw-r--r-- | lib/VNDB/DB/Users.pm | 28 |
1 files changed, 22 insertions, 6 deletions
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index b2cd1a31..593c6415 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -5,11 +5,11 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbSessionAdd dbSessionDel dbSessionCheck|; +our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel dbSessionCheck|; # %options->{ username passwd mail order uid ip registered search results page what } -# what: stats mymessages +# what: stats extended sub dbUserGet { my $s = shift; my %o = ( @@ -43,8 +43,12 @@ sub dbUserGet { ); my @select = ( - qw|id username mail rank salt c_votes c_changes show_nsfw show_list skin customcss ip c_tags ign_votes|, - q|encode(passwd, 'hex') AS passwd|, q|extract('epoch' from registered) as registered|, + qw|id username c_votes c_changes show_list c_tags|, + q|extract('epoch' from registered) as registered|, + $o{what} =~ /extended/ ? ( + qw|mail rank salt skin customcss show_nsfw ign_votes|, + q|encode(passwd, 'hex') AS passwd| + ) : (), $o{what} =~ /stats/ ? ( '(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount', '(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount', @@ -53,8 +57,6 @@ sub dbUserGet { '(SELECT COUNT(DISTINCT tag) FROM tags_vn WHERE uid = u.id) AS tagcount', '(SELECT COUNT(DISTINCT vid) FROM tags_vn WHERE uid = u.id) AS tagvncount', ) : (), - $o{what} =~ /mymessages/ ? - '(SELECT COUNT(*) FROM threads_boards tb JOIN threads t ON t.id = tb.tid WHERE tb.type = \'u\' AND tb.iid = u.id AND t.hidden = FALSE) AS mymessages' : (), ); my($r, $np) = $s->dbPage(\%o, q| @@ -110,6 +112,20 @@ sub dbUserDel { } +# Returns number of unread messages +sub dbUserMessageCount { # uid + my($s, $uid) = @_; + return $s->dbRow(q{ + SELECT SUM(tbi.count) AS cnt FROM ( + SELECT t.count-COALESCE(tb.lastread,0) + FROM threads_boards tb + JOIN threads t ON t.id = tb.tid AND NOT t.hidden + WHERE tb.type = 'u' AND tb.iid = ? + ) AS tbi (count) + }, $uid)->{cnt}||0; +} + + # Adds a session to the database # If no expiration is supplied the database default is used # uid, 40 character session token, expiration time (timestamp) |