src/newslash_web/lib/Newslash/Model/Polls.pm

   1 package Newslash::Model::Polls;
   2 use Newslash::Model::Base -base;
   3 use Newslash::Model::Polls::Voters;
   4 use Data::Dumper;
   5
   6
   7 sub count {
   8     my $self = shift;
   9     return $self->generic_count(table => "pollquestions",
  10                                 target => "qid",
  11                                 timestamp => "date",
  12                                 @_);
  13 }
  14
  15 sub answers {
  16     return shift->new_instance_of("::Polls::Answers");
  17 };
  18
  19 sub voters {
  20     return shift->new_instance_of("::Polls::Voters");
  21 };
  22
  23 sub select {
  24     my $self = shift;
  25     my $params = {@_};
  26
  27     my $unique_keys = { question_id => "pollquestions.qid",
  28                         qid => "pollquestions.qid",
  29                         id => "pollquestions.qid",
  30                         discussion => "pollquestions.discussion",
  31                         discussion_id => "pollquestions.discussion",
  32                       };
  33     my $keys = { create_time => "pollquestions.date",
  34                };
  35     my $datetime_keys = { create_time => "pollquestions.date",
  36                         };
  37     my $timestamp = "pollquestions.date";
  38
  39     my ($where_clause, $where_values, $unique) = $self->build_where_clause(unique_keys => $unique_keys,
  40                                                                            keys => $keys,
  41                                                                            datetime_keys => $datetime_keys,
  42                                                                            timestamp => $timestamp,
  43                                                                            params => $params);
  44     my ($limit_clause, $limit_values) = $self->build_limit_clause(params => $params);
  45     my ($orderby_clause, $orderby_values) = $self->build_order_by_clause(keys => $keys,
  46                                                                          params => $params);
  47
  48     $limit_clause = "LIMIT 20" if !$limit_clause;
  49
  50     # show future story?
  51     my @where_clauses;
  52     if (!$params->{show_future}) {
  53         push @where_clauses, "pollquestions.date <= NOW()";
  54     }
  55     if (@where_clauses) {
  56         if ($where_clause) {
  57             $where_clause = $where_clause . " AND ";
  58         }
  59         else {
  60             $where_clause = "WHERE ";
  61         }
  62         $where_clause = $where_clause . join(" AND ", @where_clauses);
  63     }
  64
  65     my @attrs;
  66     push @attrs, @$where_values, @$limit_values, @$orderby_values;
  67
  68     my $sql = <<"EOSQL";
  69 SELECT pollquestions.*,
  70        users.nickname AS author,
  71        topics.*,
  72        discussions.type AS discussion_type, discussions.commentcount AS comment_count
  73   FROM pollquestions
  74     LEFT JOIN users ON pollquestions.uid = users.uid
  75     LEFT JOIN discussions ON pollquestions.discussion = discussions.id
  76     LEFT JOIN topics ON pollquestions.topic = topics.tid
  77   $where_clause
  78   $orderby_clause
  79   $limit_clause
  80 EOSQL
  81
  82     my $dbh = $self->connect_db;
  83     my $sth = $dbh->prepare($sql);
  84     $sth->execute(@attrs);
  85     my $polls = $sth->fetchall_arrayref(+{});
  86     if (!$polls) {
  87         $self->disconnect_db();
  88         return;
  89     }
  90     $self->disconnect_db();
  91
  92     for my $poll (@$polls) {
  93         $self->_generalize($poll);
  94         my $answers = $self->generic_select(table => "pollanswers",
  95                                             keys => [qw(qid aid)],
  96                                             params => {
  97                                                        qid => $poll->{qid},
  98                                                        order_by => { aid => 'ASC' },
  99                                                       });
 100         next if !$answers;
 101         $poll->{answers} = $answers;
 102         $self->_calc_percentage($answers);
 103     }
 104
 105     if ($unique) {
 106         return $polls->[0];
 107     }
 108
 109     return $polls;
 110 }
 111
 112 sub _calc_percentage {
 113     my ($self, $answers) = @_;
 114     my $total = 0;
 115     for my $answer (@$answers) {
 116         $total += $answer->{votes};
 117     }
 118     for my $answer (@$answers) {
 119         if ($total > 0) {
 120             $answer->{percentage} = int(100 * $answer->{votes} / $total);
 121         }
 122     }
 123 }
 124
 125 sub create {
 126     my ($self, $user, $params) = @_;
 127     return if $self->check_readonly;
 128     return if !$user || !$params;
 129
 130     my $sql = <<"EOSQL";
 131 INSERT INTO pollquestions
 132     (question, voters, topic, date,  uid, primaryskid)
 133   VALUES
 134     (?,        0,      ?,     NOW(), ?,   ?)
 135 EOSQL
 136
 137     my $dbh = $self->start_transaction;
 138     my $rs = $dbh->do($sql,
 139                       undef,
 140                       $params->{question},
 141                       $params->{tid},
 142                       $user->{uid},
 143                       1);
 144     if (!$rs) {
 145         $dbh->rollback;
 146         $self->set_error("insert question failed", $dbh->{mysql_errorno});
 147         $self->disconnect_db;
 148         return;
 149     }
 150     my $qid = $dbh->last_insert_id(undef, undef, undef, undef);
 151
 152     $sql = <<"EOSQL";
 153 INSERT INTO pollanswers
 154     (qid, aid, answer, votes)
 155   VALUES
 156     (?,   ?,   ?,      0)
 157 EOSQL
 158
 159     my $aid = 0;
 160     for my $answer (@{$params->{answers}}) {
 161         my $rs = $dbh->do($sql, undef, $qid, $aid, $answer);
 162         if (!$rs) {
 163             $dbh->rollback;
 164             $self->set_error("insert answer failed", $dbh->{mysql_errorno});
 165             $self->disconnect_db;
 166             return;
 167         }
 168         $aid++;
 169     }
 170
 171     $self->commit;
 172     return $qid;
 173 }
 174
 175 sub vote {
 176     my ($self, $user, $qid, $aid) = @_;
 177     my $uid = $user ? $user->{uid} : 0;
 178
 179     if (!$uid) {
 180         $self->set_error("invalid user");
 181         return;
 182     }
 183     my $poll = $self->select(question_id => $qid || 0);
 184     if (!$poll) {
 185         $self->set_error("poll not found");
 186         return;
 187     }
 188
 189     my $is_valid_answer = 0;
 190     if ($aid) {
 191         for my $answer (@{$poll->{answers}}) {
 192             if ($aid == $answer->{aid}) {
 193                 $is_valid_answer = 1;
 194                 last;
 195             }
 196         }
 197     }
 198     if (!$is_valid_answer) {
 199         $self->set_error("invalid answer");
 200         return;
 201     }
 202
 203     # already voted?
 204     if ($user->{is_login}) {
 205         my $voters = $self->voters;
 206         my $votes = $voters->select(question_id => $qid, user_id => $uid);
 207         if (!$votes) {
 208             $self->set_error("select voters failed", $voters->last_errorno);
 209             return;
 210         }
 211         if (@$votes > 0) {
 212             $self->set_error("already voted");
 213             return;
 214         }
 215     }
 216
 217     # update tables
 218     my $sql = <<"EOSQL";
 219 UPDATE pollanswers SET votes = votes + 1 WHERE qid = ? AND aid = ?
 220 EOSQL
 221
 222     my $dbh = $self->start_transaction;
 223     my $rs = $dbh->do($sql, undef, $qid, $aid);
 224     if (!$rs) {
 225         $dbh->rollback;
 226         $self->set_error("update pollanswers failed", $dbh->{mysql_errorno});
 227         $self->disconnect_db;
 228         return;
 229     }
 230
 231     $sql = <<"EOSQL";
 232 UPDATE pollquestions SET voters = voters + 1 WHERE qid = ?
 233 EOSQL
 234
 235     $rs = $dbh->do($sql, undef, $qid);
 236     if (!$rs) {
 237         $dbh->rollback;
 238         $self->set_error("update pollquestions failed", $dbh->{mysql_errorno});
 239         $self->disconnect_db;
 240         return;
 241     }
 242
 243     $sql = <<"EOSQL";
 244 INSERT INTO pollvoters
 245     (qid, id, time,  uid)
 246   VALUES
 247     (?,    ?, NOW(), ?)
 248 EOSQL
 249
 250     my $net_id = $user->{ipid} || "";
 251
 252     $rs = $dbh->do($sql, undef, $qid, $net_id, $uid);
 253     if (!$rs) {
 254         $dbh->rollback;
 255         $self->set_error("insert into pollvoters failed", $dbh->{mysql_errorno});
 256         $self->disconnect_db;
 257         return;
 258     }
 259
 260     $self->commit;
 261     return 1;
 262 }
 263
 264 sub hard_delete {
 265     my ($self, $qid) = @_;
 266     return if !$qid;
 267
 268     my $sql = "DELETE FROM pollanswers WHERE qid = ?";
 269
 270     my $dbh = $self->start_transaction;
 271     my $rs = $dbh->do($sql, undef, $qid);
 272     if (!$rs) {
 273         $dbh->rollback;
 274         $self->set_error("hard-delete answers failed", $dbh->{mysql_errorno});
 275         $self->disconnect_db;
 276         return;
 277     }
 278
 279     $sql = "DELETE FROM pollquestions WHERE qid = ?";
 280     $rs = $dbh->do($sql, undef, $qid);
 281     if (!$rs) {
 282         $dbh->rollback;
 283         $self->set_error("hard-delete questions failed", $dbh->{mysql_errorno});
 284         $self->disconnect_db;
 285         return;
 286     }
 287
 288     $sql = "DELETE FROM pollvoters WHERE qid = ?";
 289     $rs = $dbh->do($sql, undef, $qid);
 290     if (!$rs) {
 291         $dbh->rollback;
 292         $self->set_error("hard-delete voters failed", $dbh->{mysql_errorno});
 293         $self->disconnect_db;
 294         return;
 295     }
 296
 297     $self->commit;
 298     return 1;
 299 }
 300
 301 sub _generalize {
 302     my ($self, $poll) = @_;
 303
 304     # NTO-nized
 305     $poll->{id} = $poll->{qid};
 306     $poll->{create_time} = $poll->{date};
 307
 308     my $primary_topic = {};
 309     for my $item (qw(tid keyword textname series image width height submittable searchable storypickable usesprite)) {
 310         $primary_topic->{$item} = $poll->{$item};
 311     }
 312     $poll->{primary_topic} = $primary_topic;
 313     $poll->{topics} = [$primary_topic,];
 314
 315     if ($poll->{topic}) {
 316         $poll->{primary_topic} = $poll->{topic};
 317     }
 318     $poll->{content_type} = "poll";
 319     $poll->{title} = $poll->{question};
 320     $poll->{discussion_id} = $poll->{discussion};
 321     $poll->{public} = 'yes';
 322
 323     return $poll;
 324 }
 325
 326
 327 1;