source: trunk/lib/Epoll/DB/Poll.pm @ 319

package Epoll::DB::Poll;

# $Id$

use strict;
use warnings;
use base 'Epoll::DB::common';
use Crypt::RSA;
use Crypt::RSA::Key::Public::SSH;
use Crypt::RSA::Key::Private::SSH;
use MIME::Base64;
use Epoll::DB::Ballot;
use Epoll::DB::Voting;
use Epoll::DB::Choice;
use Epoll::DB::Poll::Bdata;
use Epoll::DB::Poll::BSubmit;
use YAML;

=head1 NAME

Epoll::Model::Vote - Catalyst Model

=head1 DESCRIPTION

Catalyst Model.

=head1 POLL PROPERTIES

=head2 INLINES PROPERTIES

=over 4

=item id

Unique identifier

=item label

The poll label

=item start

The timestamp when poll start

=item end

The timestamp when poll stop

=item password

The poll password encrypted using crypt()

=item owner

The mail of the owner

=back

=head2 EXTENDED PROPERTIES

=over 4

=item uid

The anonyme uid of the poll (to use in url)

=item choice_count

The count of possible choice in ballot

=item elected_choice

Count of select from voting list

=item free_choice 

The count of free text input on ballot

=item description

The long description of the poll

=item procedure

A text about rules of the poll

=item private_key

The RSA private key of the poll (if any)

=item public_key

The RSA public key of the poll (if any)

=item no_mail_confirm_vote

Do not sent mail to voting to confirm vote

=item no_mail_ballot_id

Don't include ballot id in mail confirmation

=item no_ballot_id_voting

Do not give ballot's id to voting people on website

=back

=cut

sub new {
    my ($class, $dbstring, $voteid) = @_;
    
    $voteid && $voteid =~ /^\d+$/ or return;

    my $poll = bless {
        voteid => $voteid,
        dbstring => $dbstring,
        db => Epoll::DB::common::_newdb($dbstring),
    }, $class;

    my $type = $poll->type;

    $type = ucfirst(lc($type));
    eval "require Epoll::DB::Poll::Type::$type;";
    if ($@) {
        return;
    } else {
        return bless($poll, "Epoll::DB::Poll::Type::$type");
    }
}

sub type { lc($_[0]->info('type') || 'binary') }

sub voteid { $_[0]->{voteid} }

sub uid {
    my ($self) = @_;
    # UID will come only with epoll 2.0, if no uid, using key
    return $self->info('uid') || $self->voteid;
}

sub setup {
    my ($self) = @_;
    $self->param(
        free_choice => 0,
        choice_count => 1,
        uid => Epoll::DB::common::random_string(),
    );
}

sub _online_f { qw(label start end owner password) }

sub param {
    my ($self, %attr) = @_;

    keys %attr or return;
    my @online_f = _online_f();

    $self->db->do(q{set DATESTYLE to 'DMY'});
    if (grep { exists($attr{$_}) } @online_f) {
        my $sth = $self->db->prepare_cached(
            q{update poll set } .
            join(',', map { qq("$_" = ?) } grep { exists $attr{$_} } @online_f) .
            q{ where id = ?}
        );
        $sth->execute((map { $attr{$_} } grep { exists $attr{$_} } @online_f), $self->voteid)
            or do {
            $self->rollback;
            return;
        };
    }

    # vote settings in settings table
    foreach my $var (keys %attr) {
        grep { $var eq $_ } @online_f and next;
        $self->set_settings($var, $attr{$var});
    }
    1
}

sub status {
    my ($self) = @_;
    
    my $sth = $self->db->prepare_cached(
        q{
        select (start > now() or start is null) as before,
               "end" < now() as after
        from poll
        where id = ?
        }
    );
    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res or return;
    if ($res->{before}) {
        return 'BEFORE';
    } elsif ($res->{after}) {
        return 'AFTER';
    } else {
        return 'RUNNING';
    }
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select *,
        to_char("start", 'DD/MM/YYYY') as dstart,
        to_char("start", 'HH24:MI:SS') as hstart,
        to_char("end", 'DD/MM/YYYY') as dend,
        to_char("end", 'HH24:MI:SS') as hend
        from poll where id = ?
        }
    );

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

sub raw_info {
    my ($self, $var) = @_;

    if ($var) {
        if (grep { $var eq $_ } (_online_f(), qw(dstart hstart dend hend))) {
            return ( $self->_info || {} )->{$var};
        } else {
            my $sth = $self->db->prepare_cached(
                q{select val from settings where poll = ? and var = ?}
            );
            $sth->execute($self->voteid, $var);
            my $res = $sth->fetchrow_hashref;
            $sth->finish;
            return $res->{val}
        }
    }

    if (my $res = $self->_info) {
        my $get = $self->db->prepare_cached(
            q{select var, val from settings where poll = ?}
        );
        $get->execute($self->voteid);
        while (my $set = $get->fetchrow_hashref) {
            $res->{$set->{var}} = $set->{val};
        }
        return $res
    }
    return;
}

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

    my $default = {
        free_choice => 0, # avoid undef in some case
        elected_count => $self->raw_info('choice_count'),
    };

    if ($var) {
        my $val = $self->raw_info($var);
        return defined($val)
            ? $val
            : $default->{$var};
    } elsif (my $res = $self->raw_info) {
        foreach (keys %$default) {
            $res->{$_} = $default->{$_} if (!defined($res->{$_}));
        }
        return $res
    }
    return;
}

sub set_settings {
    my ($self, $var, $val) = @_;

    $val = undef if(defined($val) && $val eq '');
    my $upd = $self->db->prepare_cached(
        q{update settings set val = ? where poll = ? and var = ?}
    );

    if ($upd->execute($val, $self->voteid, $var) == 0) {
        my $add = $self->db->prepare_cached(
            q{insert into settings (poll, var, val) values (?,?,?)}
        );

        $add->execute($self->voteid, $var, $val);
    }
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select *, voting.key as vkey from voting left join signing
        on signing.key = voting.key
        where poll = ? order by voting.mail
        }
    );
    $sth->execute($self->voteid);
    my @people;
    while (my $res = $sth->fetchrow_hashref) {
        push(@people, $res);
    }
    @people
}

sub voting {
    my ($self, $votingkey) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select key from voting where poll = ? and key = ?
        }
    );

    $sth->execute($self->voteid, $votingkey);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    return $res ? Epoll::DB::Voting->new($self->{dbstring}, $votingkey) : undef;
}

sub voting_from_mail {
    my ($self, $mail) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select key from voting where poll = ? and mail = ?
        }
    );

    $sth->execute($self->voteid, $mail);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    return $res ? Epoll::DB::Voting->new($self->{dbstring}, $res->{key}) : undef;
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select key from voting
        where poll = ? order by voting.mail
        }
    );
    $sth->execute($self->voteid);
    my @people;
    while (my $res = $sth->fetchrow_hashref) {
        push(@people, $res->{key});
    }
    @people
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select *, voting.key as vkey from voting left join signing
        on signing.key = voting.key
        where voting.key = ?
        }
    );
    $sth->execute($self->voteid);

    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res
}

sub choice {
    my ($self, $chid) = @_;
    
    my $sth = $self->db->prepare_cached(
        q{
        select key from choice where poll = ? and key = ?
        }
    );
    $sth->execute($self->voteid, $chid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    return $res ? Epoll::DB::Choice->new($self->{dbstring}, $chid) : undef;
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select key from choice where poll = ?
        order by label
        }
    );
    $sth->execute($self->voteid);
    my @ch;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ch, $res->{key});
    }
    @ch
}

# TODO: replaced, to kill
sub choices {
    my ($self) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select key from choice where poll = ?
        order by label
        }
    );
    $sth->execute($self->voteid);
    my @ch;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ch, $res->{key});
    }
    @ch
}

sub add_choice {
    my ($self, $label) = @_;

    my $sth = $self->db->prepare_cached(
        q{insert into choice (poll, label) values (?,?)}
    );

    $sth->execute($self->voteid, $label) or do {
        $self->rollback;
        return;
    };

    1
}

sub delete_choice {
    my ($self, $chid) = @_;

    my $sth = $self->db->prepare_cached(
        q{delete from choice where key = ?}
    );

    $sth->execute($chid);
}

sub _register_signing {
    my ($self, $mail, $referal) = @_;

    my $vinfo = $self->voting_info_id($mail) or return;

    my $sth = $self->db->prepare_cached(
        q{
        insert into signing (key, referal) values (?,?)
        }
    );
    $sth->execute($vinfo->{key}, $referal) or do {
        $self->rollback;
        return;
    };

    1;
}

sub find_choice_key {
    my ($self, $value) = @_;

    my $sth = $self->db->prepare_cached(
        q{select key from choice where lower(label) = ? and poll = ?}
    );
    $sth->execute(lc($value), $self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{key}
}

sub decrypted_ballots {
    my ($self, $password) = @_;
    my $privkey;
    if ($self->is_crypted) {
        $privkey = $self->private_key($password) or return;
    }
    my $fetch_data = $self->db->prepare_cached(
        q{select * from ballot_enc where decrypted = false and poll = ?
            order by id}
    );
    $fetch_data->execute($self->voteid);
    while (my $res = $fetch_data->fetchrow_hashref) {
        my $bdata = $self->bdata;
        $bdata->decrypt_data($res->{id}, $res->{data}, $res->{enckey}, $privkey)
            and return;
    }
    1
}

sub register_ballot {
    my ($self, $vmail, $choice, $referal) = @_;
    # TODO: warn, deprecated

    my $bdata = $self->bsubmit;
    if (!$choice) {
    } elsif (ref $choice eq 'ARRAY') {
        foreach (@{ $choice || []}) {
            $bdata->add_item($_, 1);
        }
    } else {
        foreach (keys %{ $choice || {}}) {
            $bdata->add_item($_, $choice->{$_});
        }
    }
    $bdata->set_voter($vmail, $referal);
    return $bdata->submit($vmail, $referal);
}

sub is_crypted {
    my ($self) = @_;
    return $self->info->{public_key} ? 1 : 0;
}

sub voting_info_id {
    my ($self, $mail) = @_;

    my $sth = $self->db->prepare_cached(
        q{
        select * from voting where mail = ? and poll = ?
        }
    );
    $sth->execute($mail, $self->voteid);
    my $res = $sth->fetchrow_hashref();
    $sth->finish;
    $res
}

sub auth_voting {
    my ($self, $mail, $password) = @_;
    if (my $voter = $self->voting_from_mail($mail)) {
        return $voter->auth($password);
    } else {
        return;
    }
}

sub auth_poll {
    my ($self, $passwd) = @_;

    my $vinfo = $self->info or return;

    $vinfo->{password} or return;
    $passwd or return;
    if (crypt($passwd, $vinfo->{password} || '') eq $vinfo->{password}) {
        return 1;
    } else {
        return 0;
    }
}

sub voting_has_sign {
    my ($self, $user) = @_;
    $self->voting_from_mail($user)->has_sign;
}

# Requete de decompte des voix:

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

    # If ballot are encrypted, no
    if ($self->list_ballot_need_dec) {
        return;
    }

    return 1;
}

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

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

sub ballot {
    my ($self, $id) = @_;

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

    $sth->execute($self->voteid, $id);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    return $res ? Epoll::DB::Ballot->new($self->{dbstring}, $id) : undef;
}

# TODO kill this:
sub list_ballot {
    ballot_keys(@_);
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot where poll = ?
        order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot_enc where poll = ?
        order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot_enc where poll = ? and decrypted = 'false'
        order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot where poll = ?
        and invalid is null order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot where poll = ?
        and invalid = false order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select id from ballot where poll = ?
        and invalid = true order by id
        }
    );
    $sth->execute($self->voteid);
    my @ids;
    while (my $res = $sth->fetchrow_hashref) {
        push(@ids, $res->{id});
    }
    @ids
}

sub ballot_untrusted_values {
    my ($self, $noinvalid) = @_;

    my $getval = $self->db->prepare_cached(
        q{
        select value from ballot join ballot_item
        on ballot.id = ballot_item.id
        where poll = ? and fromlist = false
        } . ($noinvalid
            ? ' and (ballot.invalid = false or ballot.invalid is null) '
            : ''
        ) . q{
        group by value order by value
        }
    );
    $getval->execute($self->voteid);
    my @vals;
    while (my $res = $getval->fetchrow_hashref) {
        push(@vals, $res->{value});
    }
    @vals
}

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

    my $getval = $self->db->prepare_cached(
        q{
        select ballot.id from ballot join ballot_item
        on ballot.id = ballot_item.id
        where poll = ? and fromlist = false
        group by ballot.id order by id
        }
    );
    $getval->execute($self->voteid);
    my @vals;
    while (my $res = $getval->fetchrow_hashref) {
        push(@vals, $res->{id});
    }
    @vals
}

sub ballot_by_value {
    my ($self, $value) = @_;

    my $getval = $self->db->prepare_cached(
        q{
        select ballot.id from ballot join ballot_item on ballot.id =
        ballot_item.id
        where ballot.poll = ? and "value" = ?
        group by ballot.id order by id
        }
    );
    $getval->execute($self->voteid, $value);
    my @vals;
    while (my $res = $getval->fetchrow_hashref) {
        push(@vals, $res->{id});
    }
    @vals
}

sub ballot_values {
    my ($self, $noinvalid) = @_;

    my $getval = $self->db->prepare_cached(
        q{
        select value as value from ballot join ballot_item
        on ballot.id = ballot_item.id
        where poll = ?
        } . ($noinvalid
            ? ' and (ballot.invalid = false or ballot.invalid is null) '
            : ''
        ) . q{
        group by value order by value
        }
    );
    $getval->execute($self->voteid);
    my @vals;
    while (my $res = $getval->fetchrow_hashref) {
        push(@vals, $res->{value});
    }
    @vals
}

sub value_map_to {
    my ($self, $from) = @_;
    my $sth = $self->db->prepare_cached(
        q{select "to" from ballot_map where poll = ? and "from" = ?}
    );
    $sth->execute($self->voteid, $from);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{to}
}

sub map_value {
    my ($self, $from, $to) = @_;

    if (!$to) {
        my $sth = $self->db->prepare_cached(
            q{delete from ballot_map where poll = ? and "from" = ?}
        );
        $sth->execute($self->voteid, $from);
    } else {
        my $sthup = $self->db->prepare_cached(
            q{update ballot_map set "to" = ? where poll = ? and "from" = ?}
        );
        if ($sthup->execute($to, $self->voteid, $from) == 0) {
            my $sth = $self->db->prepare_cached(
                q{
                insert into ballot_map (poll, "from", "to") values (?,?,?)
                }
            );

            $sth->execute($self->voteid, $from, $to) or do {
                $self->rollback;
                return;
            };
        }
    }
    $self->commit;
    return 1;
}

sub addupd_voting {
    my ($self, $mail, $id, $extern_auth, $extern_uid) = @_;

    $mail =~ s/\s*$//;
    $mail =~ s/^\s*//;
    $mail = lc($mail);
    $id ||= '';
    $id =~ s/\s*$//;
    $id =~ s/^\s//;
    my $upd = $self->db->prepare_cached(
        q{
        update voting set label = ?, extern_auth = ?, extern_uid = ? where mail = ? and poll = ?
        }
    );

    if ($upd->execute($id, $extern_auth, $extern_uid, $mail, $self->voteid) == 0) {
        my $add = $self->db->prepare_cached(q{
            insert into voting (poll, label, mail, extern_auth, extern_uid)
            values (?,?,?,?,?)
        });

        return $add->execute($self->voteid, $id || '', $mail, $extern_auth, $extern_uid);
    } else {
        return 1;
    }
}

sub voting_from_file {
    my ($self, $fh, $delete) = @_;

    if ($delete) {
        my $sth = $self->db->prepare(q{delete from voting where poll = ?});
        $sth->execute($self->voteid);
    }

    my $handle = $self->import_handle('csv'); # std import
    $handle->{handle} = $fh;
    my @list = $handle->fetch_voters or do {
            $self->rollback;
            return;
    };
    foreach (@list) {
        $self->addupd_voting($_->[0], $_->[1] || '');
    }
    1;
}

sub voting_from_import {
    my ($self, $handle, %options) = @_;

    my $ext_auth = $options{ext_auth} && $handle->can_authenticate;
    my $authid;
    if ($ext_auth || $options{save_param}) {
        my $find_next_id = $self->db->prepare_cached(
            q{select nextval('extern_auth_id_seq'::regclass)}
        );
        $find_next_id->execute();
        $authid = $find_next_id->fetchrow_hashref->{nextval};
        $find_next_id->finish;

        my $add_auth = $self->db->prepare_cached(
            q{insert into extern_auth (id, params, owner, auth_type, "desc") values
            (?,?,?,?,?)}
        );
        $add_auth->execute($authid, $handle->xml_params, $self->info('owner'),
            $handle->auth_type, $options{desc});
    }

    my @list = $handle->fetch_voters or do {
        $self->rollback;
        return;
    };
    foreach (@list) {
        $self->addupd_voting(
            $_->[0], $_->[1] || '', $authid, ($ext_auth ? $_->[2]: undef)
        ) or do {
            $self->db->rollback;
            return;
        };
    }

    return 1;
}

sub delete_voting {
    my ($self, $key) = @_;
    $self->voting($key)->has_sign and return;
    my $sth = $self->db->prepare_cached(
        q{delete from voting where key = ? and poll = ?}
    );

    $sth->execute($key, $self->voteid);
}

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

    my $list_voting = $self->db->prepare_cached(
        q{select key from voting where poll = ? and passwd is null or passwd = ''}
    );

    $list_voting->execute($self->voteid);
    my @ids;
    while (my $res = $list_voting->fetchrow_hashref) {
        push(@ids, $res->{key});
    }
    @ids
}

sub mail_voting_passwd {
    my ($self, $id, $mailinfo) = @_;
    $self->voting($id)->mail_voting_passwd($mailinfo); 
}

# crypto part

sub rsa {
    my ($self) = @_;
    $self->{rsa} ||= new Crypt::RSA ES => 'PKCS1v15';
}

sub gen_poll_keys {
    my ($self, $password) = @_;
    my ($public, $private) = $self->rsa->keygen (
        Identity  => 'Epoll Vote ' . $self->voteid,
        Size      => 768,
        Password  => $password,
        Verbosity => 0,
        KF=>'SSH',
    ) or die $self->rsa->errstr(); # TODO avoid die
    $self->param(
        public_key => $public->serialize,
        private_key => encode_base64($private->serialize),
    );
}

sub public_key {
    my ($self) = @_;
    my $serialize = $self->info->{public_key} or return;
    my $pubkey = Crypt::RSA::Key::Public::SSH->new;
    $pubkey->deserialize(String => [ $serialize ]);
    $pubkey
}

sub private_key {
    my ($self, $password) = @_;
    my $serialize = $self->info->{private_key} or return;
    my $privkey = Crypt::RSA::Key::Private::SSH->new;
    $privkey->deserialize(String => [ decode_base64($serialize) ], Passphrase => $password);
    $privkey
}



#########
# Count #
#########

sub ballot_count {
    ballots_count(@_);
}

sub ballots_count {
    my ($self) = @_;
    return $self->ballot_count_crypt;
}

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

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

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count} || 0
}

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

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

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count} || 0
}

sub voting_count { voters_count(@_) }

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from voting
        where poll = ?
        }
    );
    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count}
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from signing join voting
        on voting.key = signing.key where poll = ?
        }
    );

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count}
}

sub not_signing_count {
    my ($self) = @_;
    $self->voters_count - $self->signing_count;
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from ballot where poll = ?
            and (invalid = 'f' or invalid is NULL)
        }
    );

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count}
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from ballot where poll = ?
        and invalid = 't'
        }
    );

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count} || 0
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from ballot where poll = ?
        and id not in (select id from ballot_item)
        and (invalid = 'false' or invalid is null)
        }
    );

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count}
}

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

    my $sth = $self->db->prepare_cached(
        q{
        select count(*) from ballot where poll = ?
        and id in (select id from ballot_item)
        and (invalid = 'false' or invalid is null)
        }
    );

    $sth->execute($self->voteid);
    my $res = $sth->fetchrow_hashref;
    $sth->finish;
    $res->{count} || 0
}

sub compute_results {
    my ($self) = @_;
    $self->param('static_results',
        YAML::Dump($self->_compute_results));
}

sub results {
    my ($self) = @_;
    if (my $res = YAML::Load($self->info('static_results'))) {
        return $res
    } else {
        return;
    }
}

#################
# CLEANING DATA #
#################

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

    $self->store_results;
    $self->_delete_ballot;
    $self->commit;
}

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

    foreach (
        q{delete from ballot_item where id in (select id from ballot where
        ballot.poll = ?)},
        q{delete from ballot where poll = ?},
        q{delete from ballot_enc where poll = ?},) {
        my $req = $self->db->prepare($_);
        $req->execute($self->voteid) or return;
    }

    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;