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