[3] | 1 | package Vote::Model::Vote; |
---|
| 2 | |
---|
| 3 | use strict; |
---|
| 4 | use warnings; |
---|
| 5 | use base 'Catalyst::Model'; |
---|
[15] | 6 | use Vote; |
---|
[3] | 7 | use DBI; |
---|
[28] | 8 | use Mail::Mailer; |
---|
[3] | 9 | |
---|
| 10 | =head1 NAME |
---|
| 11 | |
---|
| 12 | Vote::Model::Vote - Catalyst Model |
---|
| 13 | |
---|
| 14 | =head1 DESCRIPTION |
---|
| 15 | |
---|
| 16 | Catalyst Model. |
---|
| 17 | |
---|
| 18 | =cut |
---|
| 19 | |
---|
| 20 | sub new { |
---|
| 21 | my ($class) = @_; |
---|
| 22 | my $db = DBI->connect( |
---|
| 23 | 'dbi:Pg:' . Vote->config->{db}, |
---|
| 24 | undef, undef, |
---|
| 25 | { |
---|
| 26 | RaiseError => 0, |
---|
| 27 | AutoCommit => 0, |
---|
| 28 | PrintWarn => 0, |
---|
| 29 | PrintError => 1, |
---|
| 30 | } |
---|
| 31 | ) or return; |
---|
| 32 | |
---|
| 33 | bless { |
---|
| 34 | db => $db, |
---|
| 35 | }, $class; |
---|
| 36 | } |
---|
| 37 | |
---|
| 38 | sub db { $_[0]->{db} } |
---|
| 39 | |
---|
[27] | 40 | sub random_string { |
---|
| 41 | my $lenght = $_[-1] || 8; |
---|
| 42 | |
---|
| 43 | return join('', map { ('a'..'z', 'A'..'Z', 0..9)[rand 62] } (1..$lenght)); |
---|
| 44 | } |
---|
| 45 | |
---|
| 46 | sub gen_enc_passwd { |
---|
| 47 | my ($self, $passwd) = @_; |
---|
| 48 | |
---|
| 49 | $passwd ||= random_string(8); |
---|
| 50 | return(crypt($passwd, '$1$' . random_string(8) . '$')); |
---|
| 51 | } |
---|
| 52 | |
---|
[15] | 53 | sub list_comming_vote { |
---|
| 54 | my ($self) = @_; |
---|
| 55 | |
---|
| 56 | my $sth = $self->db->prepare_cached( |
---|
| 57 | q{ |
---|
| 58 | select id from poll where |
---|
| 59 | (start > now() and "end" > now()) or |
---|
| 60 | "end" is null or start is null |
---|
| 61 | } |
---|
| 62 | ); |
---|
| 63 | |
---|
| 64 | $sth->execute; |
---|
| 65 | my @id; |
---|
| 66 | while(my $res = $sth->fetchrow_hashref) { |
---|
| 67 | push(@id, $res->{id}); |
---|
| 68 | } |
---|
| 69 | |
---|
| 70 | @id |
---|
| 71 | } |
---|
| 72 | |
---|
| 73 | |
---|
[3] | 74 | sub list_running_vote { |
---|
| 75 | my ($self) = @_; |
---|
| 76 | |
---|
| 77 | my $sth = $self->db->prepare_cached( |
---|
| 78 | q{ |
---|
| 79 | select id from poll where start < now() and "end" > now() |
---|
| 80 | } |
---|
| 81 | ); |
---|
| 82 | |
---|
| 83 | $sth->execute; |
---|
| 84 | my @id; |
---|
| 85 | while(my $res = $sth->fetchrow_hashref) { |
---|
| 86 | push(@id, $res->{id}); |
---|
| 87 | } |
---|
| 88 | |
---|
| 89 | @id |
---|
| 90 | } |
---|
| 91 | |
---|
[15] | 92 | sub list_closed_vote { |
---|
| 93 | my ($self) = @_; |
---|
| 94 | |
---|
| 95 | my $sth = $self->db->prepare_cached( |
---|
| 96 | q{ |
---|
| 97 | select id from poll where |
---|
| 98 | start < now() and "end" < now() |
---|
| 99 | } |
---|
| 100 | ); |
---|
| 101 | |
---|
| 102 | $sth->execute; |
---|
| 103 | my @id; |
---|
| 104 | while(my $res = $sth->fetchrow_hashref) { |
---|
| 105 | push(@id, $res->{id}); |
---|
| 106 | } |
---|
| 107 | |
---|
| 108 | @id |
---|
| 109 | } |
---|
| 110 | |
---|
[23] | 111 | sub vote_param { |
---|
| 112 | my ($self, $voteid, %attr) = @_; |
---|
| 113 | |
---|
| 114 | keys %attr or return; |
---|
[25] | 115 | my @online_f = qw(label start end owner password); |
---|
[23] | 116 | |
---|
[32] | 117 | if (grep { exists($attr{$_}) } @online_f) { |
---|
| 118 | my $sth = $self->db->prepare_cached( |
---|
| 119 | q{update poll set } . |
---|
| 120 | join(',', map { qq("$_" = ?) } grep { exists $attr{$_} } @online_f) . |
---|
| 121 | q{ where id = ?} |
---|
| 122 | ); |
---|
| 123 | $sth->execute((map { $attr{$_} } grep { exists $attr{$_} } @online_f), $voteid) |
---|
| 124 | or do { |
---|
| 125 | $self->db->rollback; |
---|
| 126 | return; |
---|
| 127 | }; |
---|
| 128 | } |
---|
[24] | 129 | |
---|
| 130 | # vote settings in settings table |
---|
| 131 | foreach my $var (keys %attr) { |
---|
| 132 | grep { $var eq $_ } @online_f and next; |
---|
| 133 | $self->vote_set_settings($voteid, $var, $attr{$var}); |
---|
| 134 | } |
---|
| 135 | 1 |
---|
[23] | 136 | } |
---|
| 137 | |
---|
[3] | 138 | sub vote_status { |
---|
| 139 | my ($self, $id) = @_; |
---|
| 140 | |
---|
| 141 | my $sth = $self->db->prepare_cached( |
---|
| 142 | q{ |
---|
[30] | 143 | select (start > now() or start is null) as before, |
---|
[15] | 144 | "end" < now() as after |
---|
| 145 | from poll |
---|
[3] | 146 | where id = ? |
---|
| 147 | } |
---|
| 148 | ); |
---|
| 149 | $sth->execute($id); |
---|
| 150 | my $res = $sth->fetchrow_hashref; |
---|
| 151 | $sth->finish; |
---|
| 152 | $res or return; |
---|
| 153 | if ($res->{before}) { |
---|
| 154 | return 'BEFORE'; |
---|
| 155 | } elsif ($res->{after}) { |
---|
| 156 | return 'AFTER'; |
---|
| 157 | } else { |
---|
| 158 | return 'RUNNING'; |
---|
| 159 | } |
---|
| 160 | } |
---|
| 161 | |
---|
| 162 | sub vote_info { |
---|
| 163 | my ($self, $id) = @_; |
---|
| 164 | |
---|
| 165 | my $sth = $self->db->prepare_cached( |
---|
| 166 | q{ |
---|
| 167 | select * from poll where id = ? |
---|
| 168 | } |
---|
| 169 | ); |
---|
| 170 | |
---|
| 171 | $sth->execute($id); |
---|
| 172 | my $res = $sth->fetchrow_hashref; |
---|
| 173 | $sth->finish; |
---|
[24] | 174 | if ($res) { |
---|
| 175 | my $get = $self->db->prepare_cached( |
---|
| 176 | q{select var, val from settings where poll = ?} |
---|
| 177 | ); |
---|
| 178 | $get->execute($id); |
---|
| 179 | while (my $set = $get->fetchrow_hashref) { |
---|
| 180 | $res->{$set->{var}} = $set->{val}; |
---|
| 181 | } |
---|
| 182 | } |
---|
[3] | 183 | $res |
---|
| 184 | } |
---|
| 185 | |
---|
[24] | 186 | sub vote_set_settings { |
---|
| 187 | my ($self, $poll, $var, $val) = @_; |
---|
| 188 | |
---|
| 189 | my $upd = $self->db->prepare_cached( |
---|
| 190 | q{update settings set val = ? where poll = ? and var = ?} |
---|
| 191 | ); |
---|
| 192 | |
---|
| 193 | if ($upd->execute($val, $poll, $var) == 0) { |
---|
| 194 | my $add = $self->db->prepare_cached( |
---|
| 195 | q{insert into settings (poll, var, val) values (?,?,?)} |
---|
| 196 | ); |
---|
| 197 | |
---|
| 198 | $add->execute($poll, $var, $val); |
---|
| 199 | } |
---|
| 200 | } |
---|
| 201 | |
---|
[3] | 202 | sub vote_signing { |
---|
| 203 | my ($self, $id) = @_; |
---|
| 204 | |
---|
| 205 | my $sth = $self->db->prepare_cached( |
---|
| 206 | q{ |
---|
[23] | 207 | select *, voting.key as vkey from voting left join signing |
---|
[3] | 208 | on signing.key = voting.key |
---|
| 209 | where poll = ? order by voting.id |
---|
| 210 | } |
---|
| 211 | ); |
---|
| 212 | $sth->execute($id); |
---|
| 213 | my @people; |
---|
| 214 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 215 | push(@people, $res); |
---|
| 216 | } |
---|
| 217 | @people |
---|
| 218 | } |
---|
| 219 | |
---|
[23] | 220 | sub vote_voting { |
---|
| 221 | my ($self, $id) = @_; |
---|
| 222 | |
---|
| 223 | my $sth = $self->db->prepare_cached( |
---|
| 224 | q{ |
---|
| 225 | select key from voting |
---|
| 226 | where poll = ? order by voting.id |
---|
| 227 | } |
---|
| 228 | ); |
---|
| 229 | $sth->execute($id); |
---|
| 230 | my @people; |
---|
| 231 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 232 | push(@people, $res->{key}); |
---|
| 233 | } |
---|
| 234 | @people |
---|
| 235 | } |
---|
| 236 | |
---|
| 237 | sub voting_info { |
---|
| 238 | my ($self, $id) = @_; |
---|
| 239 | |
---|
| 240 | my $sth = $self->db->prepare_cached( |
---|
| 241 | q{ |
---|
| 242 | select *, voting.key as vkey from voting left join signing |
---|
| 243 | on signing.key = voting.key |
---|
| 244 | where voting.key = ? |
---|
| 245 | } |
---|
| 246 | ); |
---|
| 247 | $sth->execute($id); |
---|
| 248 | |
---|
| 249 | my $res = $sth->fetchrow_hashref; |
---|
| 250 | $sth->finish; |
---|
| 251 | $res |
---|
| 252 | } |
---|
| 253 | |
---|
[15] | 254 | sub vote_signing_count { |
---|
| 255 | my ($self, $id) = @_; |
---|
| 256 | |
---|
| 257 | my $sth = $self->db->prepare_cached( |
---|
| 258 | q{ |
---|
| 259 | select count(*) from voting |
---|
| 260 | where poll = ? |
---|
| 261 | } |
---|
| 262 | ); |
---|
| 263 | $sth->execute($id); |
---|
| 264 | my $res = $sth->fetchrow_hashref; |
---|
| 265 | $sth->finish; |
---|
| 266 | $res->{count} |
---|
| 267 | } |
---|
| 268 | |
---|
[4] | 269 | sub vote_choices { |
---|
| 270 | my ($self, $id) = @_; |
---|
| 271 | |
---|
| 272 | my $sth = $self->db->prepare_cached( |
---|
| 273 | q{ |
---|
[22] | 274 | select key from choice where poll = ? |
---|
[4] | 275 | order by label |
---|
| 276 | } |
---|
| 277 | ); |
---|
| 278 | $sth->execute($id); |
---|
| 279 | my @ch; |
---|
| 280 | while (my $res = $sth->fetchrow_hashref) { |
---|
[22] | 281 | push(@ch, $res->{key}); |
---|
[4] | 282 | } |
---|
| 283 | @ch |
---|
| 284 | } |
---|
| 285 | |
---|
[22] | 286 | sub choice_info { |
---|
| 287 | my ($self, $chid) = @_; |
---|
| 288 | my $sth = $self->db->prepare_cached( |
---|
| 289 | q{select * from choice where key = ?} |
---|
| 290 | ); |
---|
| 291 | $sth->execute($chid); |
---|
| 292 | my $res = $sth->fetchrow_hashref; |
---|
| 293 | $sth->finish; |
---|
| 294 | $res |
---|
| 295 | } |
---|
| 296 | |
---|
| 297 | sub vote_add_choice { |
---|
| 298 | my ($self, $voteid, $label) = @_; |
---|
| 299 | |
---|
| 300 | my $sth = $self->db->prepare_cached( |
---|
| 301 | q{insert into choice (poll, label) values (?,?)} |
---|
| 302 | ); |
---|
| 303 | |
---|
| 304 | $sth->execute($voteid, $label) or do { |
---|
| 305 | $self->db->rollback; |
---|
| 306 | return; |
---|
| 307 | }; |
---|
| 308 | |
---|
| 309 | 1 |
---|
| 310 | } |
---|
| 311 | |
---|
| 312 | sub modify_choice { |
---|
| 313 | my ($self, $chid, $label) = @_; |
---|
| 314 | |
---|
| 315 | my $sth = $self->db->prepare_cached( |
---|
| 316 | q{update choice set label = ? where key = ?} |
---|
| 317 | ); |
---|
| 318 | $sth->execute($label, $chid); |
---|
| 319 | } |
---|
| 320 | |
---|
| 321 | sub delete_choice { |
---|
| 322 | my ($self, $chid) = @_; |
---|
| 323 | |
---|
| 324 | my $sth = $self->db->prepare_cached( |
---|
| 325 | q{delete from choice where key = ?} |
---|
| 326 | ); |
---|
| 327 | |
---|
| 328 | $sth->execute($chid); |
---|
| 329 | } |
---|
| 330 | |
---|
[8] | 331 | sub voting_info_id { |
---|
| 332 | my ($self, $id, $voteid) = @_; |
---|
| 333 | |
---|
| 334 | my $sth = $self->db->prepare_cached( |
---|
| 335 | q{ |
---|
| 336 | select * from voting where id = ? and poll = ? |
---|
| 337 | } |
---|
| 338 | ); |
---|
| 339 | $sth->execute($id, $voteid); |
---|
| 340 | my $res = $sth->fetchrow_hashref(); |
---|
| 341 | $sth->finish; |
---|
| 342 | $res |
---|
| 343 | } |
---|
| 344 | |
---|
| 345 | sub _register_signing { |
---|
| 346 | my ($self, $vid, $voteid, $referal) = @_; |
---|
| 347 | |
---|
| 348 | my $vinfo = $self->voting_info_id($vid, $voteid) or return; |
---|
| 349 | |
---|
| 350 | my $sth = $self->db->prepare_cached( |
---|
| 351 | q{ |
---|
| 352 | insert into signing (key, referal) values (?,?) |
---|
| 353 | } |
---|
| 354 | ); |
---|
| 355 | $sth->execute($vinfo->{key}, $referal) or do { |
---|
| 356 | $self->db->rollback; |
---|
| 357 | return; |
---|
| 358 | }; |
---|
| 359 | |
---|
| 360 | 1; |
---|
| 361 | } |
---|
| 362 | |
---|
| 363 | sub gen_uid { |
---|
| 364 | unpack("H*", join("", map { chr(rand(256)) } (0..15))) |
---|
| 365 | } |
---|
| 366 | |
---|
| 367 | sub _register_ballot { |
---|
[20] | 368 | my ($self, $voteid, $choice, $fchoice) = @_; |
---|
[8] | 369 | |
---|
| 370 | my $addb = $self->db->prepare_cached( |
---|
| 371 | q{ |
---|
[20] | 372 | insert into ballot (id, poll, invalid) values (?,?,?) |
---|
[8] | 373 | } |
---|
| 374 | ); |
---|
| 375 | my $uid = gen_uid; |
---|
[20] | 376 | $addb->execute($uid, $voteid, scalar(@{$fchoice || []}) ? undef : 'f') or do { |
---|
[8] | 377 | self->db->rollback; |
---|
| 378 | return; |
---|
| 379 | }; |
---|
| 380 | |
---|
| 381 | my $addbc = $self->db->prepare_cached( |
---|
| 382 | q{ |
---|
[20] | 383 | insert into ballot_item (id, value, fromlist) values (?,?,?) |
---|
[8] | 384 | } |
---|
| 385 | ); |
---|
| 386 | foreach (@{ $choice || []}) { |
---|
[20] | 387 | $addbc->execute($uid, $_, 't') or do { |
---|
[8] | 388 | $self->db->rollback; |
---|
| 389 | return; |
---|
| 390 | }; |
---|
| 391 | } |
---|
[20] | 392 | foreach (@{ $fchoice || []}) { |
---|
| 393 | $addbc->execute($uid, $_, 'f') or do { |
---|
| 394 | $self->db->rollback; |
---|
| 395 | return; |
---|
| 396 | }; |
---|
| 397 | } |
---|
[8] | 398 | |
---|
| 399 | $uid; |
---|
| 400 | } |
---|
| 401 | |
---|
| 402 | sub register_ballot { |
---|
[20] | 403 | my ($self, $vid, $voteid, $choice, $fchoice, $referal) = @_; |
---|
[8] | 404 | |
---|
| 405 | # First we register voting has voted |
---|
| 406 | $self->_register_signing($vid, $voteid, $referal) or return; # TODO error ? |
---|
| 407 | |
---|
| 408 | # registring choices |
---|
[20] | 409 | my $uid = $self->_register_ballot($voteid, $choice, $fchoice) or return; |
---|
[8] | 410 | |
---|
| 411 | # everything went fine, saving! |
---|
| 412 | $self->db->commit; |
---|
| 413 | |
---|
| 414 | $uid |
---|
| 415 | } |
---|
| 416 | |
---|
[12] | 417 | sub signing_count { |
---|
| 418 | my ($self, $voteid) = @_; |
---|
| 419 | |
---|
| 420 | my $sth = $self->db->prepare_cached( |
---|
| 421 | q{ |
---|
| 422 | select count(*) from signing join voting |
---|
| 423 | on voting.key = signing.key where poll = ? |
---|
| 424 | } |
---|
| 425 | ); |
---|
| 426 | |
---|
| 427 | $sth->execute($voteid); |
---|
| 428 | my $res = $sth->fetchrow_hashref; |
---|
| 429 | $sth->finish; |
---|
| 430 | $res->{count} |
---|
| 431 | } |
---|
| 432 | |
---|
| 433 | sub ballot_count { |
---|
| 434 | my ($self, $voteid) = @_; |
---|
| 435 | |
---|
| 436 | my $sth = $self->db->prepare_cached( |
---|
| 437 | q{ |
---|
| 438 | select count(*) from ballot where poll = ? |
---|
| 439 | } |
---|
| 440 | ); |
---|
| 441 | |
---|
| 442 | $sth->execute($voteid); |
---|
| 443 | my $res = $sth->fetchrow_hashref; |
---|
| 444 | $sth->finish; |
---|
| 445 | $res->{count} |
---|
| 446 | } |
---|
| 447 | |
---|
[15] | 448 | sub ballot_count_nonull { |
---|
| 449 | my ($self, $voteid) = @_; |
---|
| 450 | |
---|
| 451 | my $sth = $self->db->prepare_cached( |
---|
| 452 | q{ |
---|
| 453 | select count(*) from ballot where poll = ? |
---|
[21] | 454 | and id in (select id from ballot_item) and invalid = 'false' |
---|
[15] | 455 | } |
---|
| 456 | ); |
---|
| 457 | |
---|
| 458 | $sth->execute($voteid); |
---|
| 459 | my $res = $sth->fetchrow_hashref; |
---|
| 460 | $sth->finish; |
---|
| 461 | warn $res->{count}; |
---|
| 462 | $res->{count} |
---|
| 463 | } |
---|
| 464 | |
---|
[13] | 465 | sub auth_voting { |
---|
| 466 | my ($self, $poll, $user, $password) = @_; |
---|
| 467 | my $userinfo = $self->voting_info_id($user, $poll) or return; |
---|
| 468 | |
---|
| 469 | $userinfo->{passwd} or return; |
---|
| 470 | if (crypt($password, $userinfo->{passwd} || '') eq $userinfo->{passwd}) { |
---|
| 471 | return 1; |
---|
| 472 | } else { |
---|
| 473 | return 0; |
---|
| 474 | } |
---|
| 475 | } |
---|
| 476 | |
---|
[14] | 477 | sub voting_has_sign { |
---|
| 478 | my ($self, $poll, $user) = @_; |
---|
| 479 | |
---|
| 480 | my $sth = $self->db->prepare_cached( |
---|
| 481 | q{ |
---|
| 482 | select date from signing join voting |
---|
| 483 | on voting.key = signing.key |
---|
| 484 | where poll = ? and id = ? |
---|
| 485 | } |
---|
| 486 | ); |
---|
| 487 | |
---|
| 488 | $sth->execute($poll, $user); |
---|
| 489 | my $res = $sth->fetchrow_hashref; |
---|
| 490 | $sth->finish; |
---|
| 491 | return $res->{date} |
---|
| 492 | } |
---|
| 493 | |
---|
[8] | 494 | # Requete de decompte des voix: |
---|
| 495 | |
---|
[15] | 496 | sub vote_results_count { |
---|
| 497 | my ($self, $voteid) = @_; |
---|
[8] | 498 | |
---|
[15] | 499 | my $sth = $self->db->prepare( |
---|
| 500 | q{ |
---|
| 501 | select count(ballot.id), value from ballot left join ballot_item |
---|
| 502 | on ballot.id = ballot_item.id where ballot.poll = ? and invalid = 'false' |
---|
| 503 | group by value |
---|
| 504 | order by count |
---|
| 505 | } |
---|
| 506 | ); |
---|
| 507 | $sth->execute($voteid); |
---|
| 508 | my @results; |
---|
| 509 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 510 | push(@results, $res); |
---|
| 511 | } |
---|
| 512 | @results; |
---|
| 513 | } |
---|
| 514 | |
---|
| 515 | sub vote_results_nonull { |
---|
| 516 | my ($self, $voteid) = @_; |
---|
| 517 | |
---|
| 518 | my $sth = $self->db->prepare( |
---|
| 519 | q{ |
---|
| 520 | select count(ballot.id), value from ballot join ballot_item |
---|
| 521 | on ballot.id = ballot_item.id where ballot.poll = ? and invalid = 'false' |
---|
| 522 | group by value |
---|
| 523 | order by count desc |
---|
| 524 | } |
---|
| 525 | ); |
---|
| 526 | $sth->execute($voteid); |
---|
| 527 | my @results; |
---|
| 528 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 529 | push(@results, $res); |
---|
| 530 | } |
---|
| 531 | \@results; |
---|
| 532 | } |
---|
| 533 | |
---|
[21] | 534 | sub list_vote_ballot { |
---|
| 535 | my ($self, $voteid) = @_; |
---|
| 536 | |
---|
| 537 | my $sth = $self->db->prepare_cached( |
---|
| 538 | q{ |
---|
| 539 | select id from ballot where poll = ? |
---|
| 540 | order by id |
---|
| 541 | } |
---|
| 542 | ); |
---|
| 543 | $sth->execute($voteid); |
---|
| 544 | my @ids; |
---|
| 545 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 546 | push(@ids, $res->{id}); |
---|
| 547 | } |
---|
| 548 | @ids |
---|
| 549 | } |
---|
| 550 | |
---|
| 551 | sub list_vote_ballot_needvalid { |
---|
| 552 | my ($self, $voteid) = @_; |
---|
| 553 | |
---|
| 554 | my $sth = $self->db->prepare_cached( |
---|
| 555 | q{ |
---|
| 556 | select id from ballot where poll = ? |
---|
| 557 | and invalid is null order by id |
---|
| 558 | } |
---|
| 559 | ); |
---|
| 560 | $sth->execute($voteid); |
---|
| 561 | my @ids; |
---|
| 562 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 563 | push(@ids, $res->{id}); |
---|
| 564 | } |
---|
| 565 | @ids |
---|
| 566 | } |
---|
| 567 | |
---|
| 568 | sub ballot_info { |
---|
| 569 | my ($self, $ballotid) = @_; |
---|
| 570 | |
---|
| 571 | my $sth = $self->db->prepare_cached( |
---|
| 572 | q{ select * from ballot where id = ? } |
---|
| 573 | ); |
---|
| 574 | |
---|
| 575 | $sth->execute($ballotid); |
---|
| 576 | my $res = $sth->fetchrow_hashref; |
---|
| 577 | $sth->finish; |
---|
| 578 | $res |
---|
| 579 | } |
---|
| 580 | |
---|
[29] | 581 | sub mark_ballot_invalid { |
---|
| 582 | my ($self, $ballotid, $invalid) = @_; |
---|
| 583 | |
---|
| 584 | my $sth = $self->db->prepare_cached( |
---|
| 585 | q{update ballot set invalid = ? where id = ?} |
---|
| 586 | ); |
---|
| 587 | |
---|
| 588 | $sth->execute($invalid ? 't' : 'f', $ballotid); |
---|
| 589 | } |
---|
| 590 | |
---|
[21] | 591 | sub ballot_items { |
---|
| 592 | my ($self, $ballotid) = @_; |
---|
| 593 | |
---|
| 594 | my $sth = $self->db->prepare_cached( |
---|
| 595 | q{select *, value as v from ballot_item where id = ?} |
---|
| 596 | ); |
---|
| 597 | $sth->execute($ballotid); |
---|
| 598 | my @ids; |
---|
| 599 | while (my $res = $sth->fetchrow_hashref) { |
---|
| 600 | push(@ids, $res); |
---|
| 601 | } |
---|
| 602 | @ids |
---|
| 603 | } |
---|
| 604 | |
---|
[22] | 605 | sub addupd_voting { |
---|
| 606 | my ($self, $voteid, $id, $mail) = @_; |
---|
[21] | 607 | |
---|
[22] | 608 | my $upd = $self->db->prepare_cached( |
---|
| 609 | q{ |
---|
| 610 | update voting set mail = ? where poll = ? and id = ? |
---|
| 611 | } |
---|
| 612 | ); |
---|
| 613 | |
---|
| 614 | if ($upd->execute($mail, $voteid, $id) == 0) { |
---|
| 615 | my $add = $self->db->prepare_cached(q{ |
---|
| 616 | insert into voting (poll, id, mail) values (?,?,?) |
---|
| 617 | }); |
---|
| 618 | |
---|
| 619 | $add->execute($voteid, $id, $mail); |
---|
| 620 | } |
---|
| 621 | } |
---|
| 622 | |
---|
[23] | 623 | sub delete_voting { |
---|
| 624 | my ($self, $key) = @_; |
---|
| 625 | |
---|
| 626 | my $sth = $self->db->prepare_cached( |
---|
| 627 | q{delete from voting where key = ?} |
---|
| 628 | ); |
---|
| 629 | |
---|
| 630 | $sth->execute($key); |
---|
| 631 | } |
---|
| 632 | |
---|
[22] | 633 | sub voting_from_file { |
---|
| 634 | my ($self, $voteid, $fh, $delete) = @_; |
---|
| 635 | |
---|
| 636 | if ($delete) { |
---|
| 637 | my $sth = $self->db->prepare(q{delete from voting where poll = ?}); |
---|
| 638 | $sth->execute($voteid); |
---|
| 639 | } |
---|
| 640 | |
---|
| 641 | while (my $line = <$fh>) { |
---|
| 642 | chomp($line); |
---|
| 643 | warn $line; |
---|
| 644 | my ($id, $mail) = split(';', $line); |
---|
| 645 | $id && $mail or do { |
---|
| 646 | $self->db->rollback; |
---|
| 647 | return; |
---|
| 648 | }; |
---|
| 649 | $self->addupd_voting($voteid, $id, $mail); |
---|
| 650 | } |
---|
| 651 | 1; |
---|
| 652 | } |
---|
| 653 | |
---|
[27] | 654 | sub mail_passwd_ifnul { |
---|
[28] | 655 | my ($self, $voteid, $mailinfo) = @_; |
---|
[22] | 656 | |
---|
[27] | 657 | my $list_voting = $self->db->prepare_cached( |
---|
[28] | 658 | q{select key from voting where poll = ? and passwd is null or passwd = ''} |
---|
[27] | 659 | ); |
---|
| 660 | |
---|
| 661 | $list_voting->execute($voteid); |
---|
| 662 | while (my $res = $list_voting->fetchrow_hashref) { |
---|
[28] | 663 | $self->mail_voting_passwd($res->{key}, $mailinfo); |
---|
[27] | 664 | } |
---|
| 665 | } |
---|
| 666 | |
---|
| 667 | sub mail_voting_passwd { |
---|
[28] | 668 | my ($self, $id, $mailinfo) = @_; |
---|
[27] | 669 | |
---|
| 670 | my $vinfo = $self->voting_info($id) or return; |
---|
[28] | 671 | my $voteinfo = $self->vote_info($vinfo->{poll}); |
---|
[27] | 672 | |
---|
[28] | 673 | my $passwd = random_string(8); |
---|
| 674 | my $encpasswd = $self->gen_enc_passwd($passwd); |
---|
[27] | 675 | |
---|
[28] | 676 | my $upd_voting = $self->db->prepare_cached( |
---|
| 677 | q{update voting set passwd = ? where key = ?} |
---|
| 678 | ); |
---|
| 679 | |
---|
| 680 | $upd_voting->execute($encpasswd, $id); |
---|
| 681 | |
---|
| 682 | # TODO complete this properly: |
---|
| 683 | my $mailer = new Mail::Mailer 'smtp', Server => 'mailhost'; |
---|
| 684 | $mailer->open({ |
---|
| 685 | From => 'Voting system <nomail@nomail.com>', |
---|
| 686 | To => $vinfo->{mail}, |
---|
| 687 | Subject => 'Vote passwd', |
---|
| 688 | }); |
---|
| 689 | print $mailer <<EOF; |
---|
| 690 | Vous êtes convié à voter: |
---|
| 691 | $voteinfo->{label} |
---|
| 692 | |
---|
| 693 | Votre identifiant est: $vinfo->{id} |
---|
| 694 | Votre mot de passe est: $passwd |
---|
| 695 | |
---|
| 696 | Cordialement. |
---|
| 697 | EOF |
---|
| 698 | $mailer->close; |
---|
| 699 | |
---|
| 700 | $self->db->commit; |
---|
[27] | 701 | } |
---|
| 702 | |
---|
[30] | 703 | sub poll_request_info { |
---|
| 704 | my ($self, $rid) = @_; |
---|
| 705 | |
---|
| 706 | my $sth = $self->db->prepare_cached( |
---|
| 707 | q{select * from poll_request where id = ?} |
---|
| 708 | ); |
---|
| 709 | |
---|
| 710 | $sth->execute($rid); |
---|
| 711 | my $res = $sth->fetchrow_hashref; |
---|
| 712 | $sth->finish; |
---|
| 713 | $res |
---|
| 714 | } |
---|
| 715 | |
---|
| 716 | sub poll_from_request { |
---|
| 717 | my ($self, $rid, $passwd) = @_; |
---|
| 718 | my $rinfo = $self->poll_request_info($rid) or return; |
---|
| 719 | |
---|
| 720 | my $encpasswd = $self->gen_enc_passwd($passwd); |
---|
| 721 | |
---|
| 722 | my $getpollid = $self->db->prepare_cached( |
---|
| 723 | q{select nextval('poll_id_seq')} |
---|
| 724 | ); |
---|
| 725 | $getpollid->execute(); |
---|
| 726 | my $newpollid = $getpollid->fetchrow_hashref->{nextval}; |
---|
| 727 | |
---|
| 728 | my $newpoll = $self->db->prepare_cached( |
---|
| 729 | q{insert into poll (id, label, owner, password) values (?,?,?,?)} |
---|
| 730 | ); |
---|
| 731 | |
---|
| 732 | $newpoll->execute($newpollid, $rinfo->{label}, $rinfo->{mail}, $encpasswd); |
---|
[32] | 733 | # set some default |
---|
| 734 | $self->vote_param($newpollid, |
---|
| 735 | free_choice => 0, |
---|
| 736 | choice_count => 1, |
---|
| 737 | ); |
---|
[30] | 738 | |
---|
| 739 | my $delreq = $self->db->prepare_cached( |
---|
| 740 | q{delete from poll_request where id = ?} |
---|
| 741 | ); |
---|
| 742 | |
---|
| 743 | $delreq->execute($rid); |
---|
| 744 | $self->db->commit; |
---|
| 745 | |
---|
| 746 | $newpollid |
---|
| 747 | } |
---|
| 748 | |
---|
| 749 | sub create_poll_request { |
---|
| 750 | my ($self, %info) = @_; |
---|
| 751 | |
---|
| 752 | $info{mail} or return; |
---|
| 753 | my $addreq = $self->db->prepare_cached( |
---|
| 754 | q{insert into poll_request (id, label, mail) values (?,?,?)} |
---|
| 755 | ); |
---|
| 756 | |
---|
| 757 | my $reqid = gen_uid; |
---|
| 758 | |
---|
| 759 | $addreq->execute($reqid, $info{label}, $info{mail}); |
---|
| 760 | my $mailer = new Mail::Mailer 'smtp', Server => 'mailhost'; |
---|
| 761 | $mailer->open({ |
---|
| 762 | From => 'Voting system <nomail@nomail.com>', |
---|
| 763 | To => $info{mail}, |
---|
| 764 | Subject => 'Votre nouveau vote', |
---|
| 765 | }); |
---|
| 766 | print $mailer <<EOF; |
---|
| 767 | |
---|
| 768 | Vous avez demandez la création d'un nouveau vote: |
---|
| 769 | $info{label} |
---|
| 770 | |
---|
| 771 | Pour valider votre demande, veuiller allez visitez la page: |
---|
| 772 | $info{url}/$reqid |
---|
| 773 | |
---|
| 774 | A bientot |
---|
| 775 | EOF |
---|
| 776 | $mailer->close; |
---|
| 777 | $self->db->commit; |
---|
| 778 | 1; |
---|
| 779 | } |
---|
| 780 | |
---|
[3] | 781 | =head1 AUTHOR |
---|
| 782 | |
---|
| 783 | Thauvin Olivier |
---|
| 784 | |
---|
| 785 | =head1 LICENSE |
---|
| 786 | |
---|
| 787 | This library is free software, you can redistribute it and/or modify |
---|
| 788 | it under the same terms as Perl itself. |
---|
| 789 | |
---|
| 790 | =cut |
---|
| 791 | |
---|
| 792 | 1; |
---|