source: trunk/lib/Epoll/DB.pm @ 303

package Epoll::DB;

# $Id$

use strict;
use warnings;
use base 'Epoll::DB::common';
use Epoll::DB::Poll;
use Epoll::DB::Poll::Results;

our $VERSION = '1.90';

=head1 NAME

Epoll::Model::Vote - Catalyst Model

=head1 DESCRIPTION

Catalyst Model.

=cut

sub new {
    my ($class, $dbstring) = @_;
    
    bless {
        dbstring => $dbstring,
        db => Epoll::DB::common::_newdb($dbstring),
    }, $class;
}

sub list_comming_vote {
    my ($self) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select id from poll where
        (start > now() and "end" > now()) or
        "end" is null or start is null
        }
    );

    $sth->execute;
    my @id;
    while(my $res = $sth->fetchrow_hashref) {
        push(@id, $res->{id});
    }

    @id
}

sub list_running_vote {
    my ($self) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select id from poll where start < now() and "end" > now()
        }
    );

    $sth->execute;
    my @id;
    while(my $res = $sth->fetchrow_hashref) {
        push(@id, $res->{id});
    }

    @id
}

sub list_closed_vote {
    my ($self) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select id from poll where
        start < now() and "end" < now()
        }
    );

    $sth->execute;
    my @id;
    while(my $res = $sth->fetchrow_hashref) {
        push(@id, $res->{id});
    }

    @id
}

sub poll {
    my ($self, $pollid) = @_;
    Epoll::DB::Poll->new($self->{dbstring}, $self->poll_id_from_uid($pollid));
}

sub results {
    my ($self, $pollid) = @_;
    Epoll::DB::Poll::Results->new($self->{dbstring}, $self->poll_id_from_uid($pollid));
}

# This part is keep to track error
sub ballot {die "# ballot" . join(' ',caller());}
sub vote_param { die "# vote_param" . join(' ',caller());}
sub vote_status {die "# vote_status" . join(' ',caller());}
sub vote_info {die "# vote_info" . join(' ',caller());}
sub vote_set_settings {die "# old functions" . join(' ',caller());}
sub vote_signing {die "# old functions" . join(' ',caller());}
sub vote_voting {die "# old functions" . join(' ',caller());}
sub voting_info {die "# old functions" . join(' ',caller());}
sub vote_choices {die "# old functions" . join(' ',caller());}
sub vote_add_choice {die "# old functions" . join(' ',caller());}
sub choice_info {die "# old functions" . join(' ',caller());}
sub delete_choice {die "# old functions" . join(' ',caller());}
sub voting_info_id {die "# old functions" . join(' ',caller());}
sub register_ballot {die "# old functions" . join(' ',caller());}
sub vote_voting_count {die "# old functions" . join(' ',caller());}
sub signing_count { vote_signing_count(@_) }
sub vote_signing_count {die "# old functions" . join(' ',caller());}
sub ballot_count { vote_ballot_count(@_) }
sub vote_ballot_count {die "# old functions" . join(' ',caller());}
sub ballot_count_nonull { vote_ballot_count_nonull(@_) }
sub vote_ballot_count_nonull {die "# old functions" . join(' ',caller());}
sub auth_voting {die "# old functions" . join(' ',caller());}
sub auth_poll {die "# old functions" . join(' ',caller());}
sub voting_has_sign {die "# old functions" . join(' ',caller());}
sub vote_results_count {die "# old functions" . join(' ',caller());}
sub vote_results_nonull {die "# old functions" . join(' ',caller());}
sub list_vote_ballot {die "# old functions" . join(' ',caller());}
sub list_vote_ballot_needvalid {die "# old functions" . join(' ',caller());}
sub ballot_info {die "# vote_status" . join(' ',caller());}
sub mark_ballot_invalid {die "# old functions" . join(' ',caller());}
sub ballot_items {die "# old functions" . join(' ',caller());}
sub vote_ballot_untrusted_values {die "# old functions" . join(' ',caller());}
sub vote_ballot_values {die "# old functions" . join(' ',caller());}
sub vote_map_value {die "# old functions" . join(' ',caller());}
sub addupd_voting {die "# old functions" . join(' ',caller());}
sub delete_voting {die "# old functions" . join(' ',caller());}
sub voting_from_file {die "# old functions" . join(' ',caller());}

sub clean_old_poll_request {
    my ($self) = @_;
    $self->db->do(q{delete from poll_request where "create" < now() - '30 days'::interval});
}

sub poll_request_info {
    my ($self, $rid) = @_;

    my $sth = $self->db->prepare_cached(
        q{select * from poll_request where id = ?}
    );

    $sth->execute($rid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res
}

sub poll_from_request {
    my ($self, $rid, $passwd) = @_;
    my $rinfo = $self->poll_request_info($rid) or return;

    my $pollid = $self->create_poll($rinfo->{mail}, $rinfo->{label}, $passwd);
    
    my $delreq = $self->db->prepare_cached(
        q{delete from poll_request where id = ?}
    );

    $delreq->execute($rid);
    $self->commit;
    $pollid
}

sub create_poll {
    my ($self, $mail, $label, $passwd) = @_;

    my $encpasswd = $self->gen_enc_passwd($passwd);

    my $getpollid = $self->db->prepare_cached(
        q{select nextval('poll_id_seq')}
    );
    $getpollid->execute();
    my $newpollid = $getpollid->fetchrow_hashref->{nextval};
    $getpollid->finish;
    
    my $newpoll = $self->db->prepare_cached(
        q{insert into poll (id, label, owner, password) values (?,?,?,?)}
    );

    $newpoll->execute($newpollid, $label, $mail, $encpasswd);
    # set some default
    $self->poll($newpollid)->setup() or do {
        $self->rollback;
        return;
    };


    $newpollid
}


sub create_poll_request {
    my ($self, %info) = @_;

    $info{mail} or return;
    my $addreq = $self->db->prepare_cached(
        q{insert into poll_request (id, label, mail) values (?,?,?)}
    );

    my $reqid = Epoll::DB::common::gen_uid();

    $addreq->execute($reqid, $info{label}, $info{mail});
    $self->commit;
    $reqid;
}

sub find_poll_by_voting {
    my ($self, $mail) = @_;
    $mail or return;
    my $finder = $self->db->prepare_cached(q{
        select poll.id, date from poll join
        subpoll on poll.id = subpoll.pollid
        join voters on voters.poll = poll.id
        left join signing on
          signing.subpoll = subpoll.id and
          signing.voters = voters.key
        where mail = lower($1)
        group by poll.id, date
        order by date desc
        }
    );
    $finder->execute($mail);
    my @results;
    while (my $res = $finder->fetchrow_hashref) {
        push(@results, $res);
    }
    @results
}

sub find_poll_by_notyet_voting {
    my ($self, $mail) = @_;
    $mail or return;
    my $finder = $self->db->prepare_cached(
        q{select poll from voting where
          key not in (select key from signing)
            and
          poll in (select id from poll where "end" > now())
            and
          mail = lower($1)
        }
    );
    $finder->execute($mail);
    my @results;
    while (my $res = $finder->fetchrow_hashref) {
        push(@results, $res);
    }
    @results
}

sub find_poll_by_owner {
    my ($self, $mail) = @_;
    $mail or return;
    my $finder = $self->db->prepare_cached(
        q{select id, "end" > now() as terminated from poll where owner = lower($1)
          order by "end" desc}
    );
    $finder->execute($mail);
    my @results;
    while (my $res = $finder->fetchrow_hashref) {
        push(@results, $res);
    }
    @results
}

sub delete_poll {
    my ($self, $id) = @_;
    {
        my $poll = $self->poll($id);
        $poll->_delete_ballot;
    }

    foreach (
        q{delete from settings where poll = ?},
        q{delete from signing where key in (select key from voting where poll = ?)},
        q{delete from voting where poll = ?},
        q{delete from choice where poll = ?},
        q{delete from ballot_map where poll = ?},
        q{delete from poll where id = ?},
    ) {
        my $req = $self->db->prepare($_);
        $req->execute($id) or do {
            $self->rollback;
            return;
        };
    }

    $self->commit;
    return 1;
}

=head1 AUTHOR

Thauvin Olivier

=head1 LICENSE

This library is free software, you can redistribute it and/or modify
it under the same terms as Perl itself or CeCILL.

=cut

1;