package LATMOS::Accounts::Bases::Sql::User;

use 5.010000;
use strict;
use warnings;
use overload '""' => 'stringify';

use LATMOS::Accounts::Utils;
use LATMOS::Accounts::Log;
use POSIX qw(strftime);
use Date::Parse qw(str2time);
use DateTime;
use DateTime::TimeZone;
use base qw(LATMOS::Accounts::Bases::Sql::objects);
use LATMOS::Accounts::I18N;
use Date::Calc;
use LATMOS::Accounts::Mail;

our $VERSION = (q$Rev$ =~ /^Rev: (\d+) /)[0];

=head1 NAME

LATMOS::Ad - Perl extension for blah blah blah

=head1 DESCRIPTION

Account base access over standard unix file format.

=head1 FUNCTIONS

=cut

=head2 new(%config)

Create a new LATMOS::Ad object for windows AD $domain.

domain / server: either the Ad domain or directly the server

ldap_args is an optionnal list of arguments to pass to L<Net::LDAP>.

=cut

sub _object_table { 'user' }

sub _key_field { 'name' }

sub _has_extended_attributes { 1 }

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

    return join(' ', grep { $_ }
        (
            $self->get_field('givenName'),
            $self->get_field('sn')
        )
    )
    || $self->get_field('description')
    || $self->id;
}

sub GetOtypeDef {
    my ($class) = @_;

    {
        address => 'user',
        employment => 'user',
    },
}

sub _get_attr_schema {
    my ($class, $base) = @_;

    my $subsetaddress = sub {
        my ($self, $data) = @_;
        my $fmainaddress = $self->object->_get_c_field('mainaddress');

        # set address attribute => create address object on the fly
        # except if attr is empty !
        if (!$fmainaddress && $data) {
            $fmainaddress = $self->object->id . '-' . join('', map { ('a'..'z')[rand(26)] }
                (0..4));
            $self->base->_create_c_object(
                'address', $fmainaddress,
                user => $self->object->id,
                isMainAddress => 1, ) or do {
                $self->base->log(LA_ERR,
                    "Cannot create main address for user %s", $self->object->id);
                return;
            };
        }
        if ($fmainaddress && 
            (my $address = $self->base->get_object('address', $fmainaddress))) {
            if ($address->attribute($self->name) &&
                !$address->attribute($self->name)->ro) {
                return $address->set_c_fields($self->name => $data) ||0;
            }
        }
    };

    my $attrs = {
            exported => {
                post => sub {
                    my ($self, $value) = @_;
                    my $attr = $self->name;

                    if (my $obj = $self->base->
                            get_object('revaliases', $self->object->id)) {
                        my $ares = $obj->set_c_fields(
                            ($attr eq 'exported' ? 'exported' : 'unexported') => $value
                        );
                        if (!defined($ares)) {
                            $self->base->log(LA_ERR,
                                'Cannot set revaliases exported attribute for user %s',
                                $self->object->id);
                        }
                    }
                    my $must_expire = $self->name eq 'exported'
                        ? ($value ? 0 : 1 )
                        : ($value ? 1 : 0 );

                    foreach my $al (grep { $_ } $self->object->get_attributes('aliases'), $self->object->id) {
                        my $obj = $self->base->get_object('aliases', $al) or next;
                        $obj->_set_c_fields(
                            exported => $value,
                        );
                    }
                    foreach my $al ($self->object->get_attributes('otheraddress')) {
                        my $obj = $self->base->get_object('address', $al) or next;
                        $obj->_set_c_fields(
                            exported => $value,
                        );
                    }
                },
            },
            uidNumber => {
                inline => 1,
                iname => 'uidnumber',
                uniq => 1,
                mandatory => 1,
                formopts => { length => 7 },
                label => l('UID'),
            },
            uidnumber => { inline => 1, hide => 1, monitored => 1 },
            gidNumber => {
                inline => 1,
                iname => 'gidnumber',
                mandatory => 1,
                can_values => sub {
                    map { $_->id, $_->get_attributes('gidNumber') }
                    map { $base->get_object('group', $_) }
                    $base->list_objects('group')
                },
                can_values => sub {
                    my $sth = $base->db->prepare_cached(
                        q{
                        select name, gidnumber from "group" where exported = true
                        }
                    );
                    $sth->execute();
                    my @res;
                    while (my $res = $sth->fetchrow_hashref) {
                        push(@res, $res->{name});
                        push(@res, $res->{gidnumber});
                    }
                    return @res;
                },
                display => sub {
                    my ($self, $val) = @_;
                    if ($val =~ /^\d+$/) {
                        my ($gr) = $self->base->search_objects('group', "gidNumber=$val")
                            or return;
                        return $gr;
                    } else {
                        my ($gr) = $self->base->search_objects('group', "name=$val")
                            or return;
                        return $gr;
                    }
                },
                input => sub {
                    my ($val) = @_;
                    $val =~ /^\d+$/ and return $val;
                    my ($gr) = $base->search_objects('group', "name=$val") or return;
                    return $base->get_object('group', $gr)->get_attributes('gidNumber');
                },
                #reference => 'group',
                label => l('GID'),
            },
            loginShell => {
                mandatory => 1,
                label => l('Shell'),
            },
            gidnumber => { inline => 1, hide => 1,
                can_values => sub {
                    map { $_->id, $_->get_attributes('gidNumber') }
                    map { $base->get_object('group', $_) }
                    $base->list_objects('group')
                },
                display => sub {
                    my ($self, $val) = @_;
                    my ($gr) = $self->base->search_objects('group', "gidNumber=$val")
                        or return;
                    return $gr;
                },
                input => sub {
                    my ($val) = @_;
                    $val =~ /^\d+$/ and return $val;
                    my ($gr) = $base->search_objects('group', "name=$val") or return;
                    return $base->get_object('group', $gr)->get_attributes('gidNumber');
                },
                mandatory => 1,
                reference => 'group',
                monitored => 1,
            },
            locked    => {
                formtype => 'CHECKBOX',
                formopts => { rawvalue => 1, },
                monitored => 1,
                label => l('Locked'),
            },
            expire        => {
                inline => 1,
                formtype => 'DATETIME',
                monitored => 1,
                label => l('Expire on'),
            },
            endcircuit    => {
                inline => 1,
                formtype => 'DATE',
                monitored => 1,
                label => l('End of entrance'),
            },
            _endEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeEndEmployment($self->base->config('employment_delay') || 0);
                },
                label => l('End of employment'),
            },
            endEmployment => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('End of employment'),
            },
            _departure => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeEndEmployment($self->base->config('employment_delay') || 0, 1, 1);
                },
                label => l('Start of employment'),
            },
            departure => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('Leaving'),
            },
            _endStrictEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeEndEmployment();
                },
            },
            endStrictEmployment => {
                formtype => 'DATETIME',
                ro => 1,
            },
            _endCurrentEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $list_empl = $self->base->db->prepare_cached(q{
                        SELECT * FROM employment WHERE "user" = ? and
                            firstday <= now() and
                            (lastday is null or lastday >= now() - '1 days'::interval) 
                            order by firstday asc
                    });
                    $list_empl->execute($self->id);
                    my $end;
                    while (my $res = $list_empl->fetchrow_hashref) {
                        if (!$res->{lastday}) {
                            # Ultimate employment.
                            $list_empl->finish;
                            return undef;
                        } else {
                            $end = DateTime->from_epoch(epoch => str2time($res->{lastday}));
                            $end->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
                            $end->add(hours => 23, minutes => 59, seconds => 59);
                        }
                        last;
                    }
                    $list_empl->finish;

                    return $end ? $end->iso8601 : undef
                },
            },
            endCurrentEmployment => {
                formtype => 'DATETIME',
                ro => 1,
            },
            _endLastEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $list_empl = $self->base->db->prepare_cached(q{
                        SELECT * FROM employment WHERE "user" = ?
                        order by lastday desc nulls first
                    });
                    $list_empl->execute($self->id);
                    my $res = $list_empl->fetchrow_hashref;
                    $list_empl->finish;
                    my $end;
                    if ($res && $res->{lastday}) {
                        $end = DateTime->from_epoch(epoch => str2time($res->{lastday}));
                        $end->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
                        $end->add(hours => 23, minutes => 59, seconds => 59);
                    }
                    return $end ? $end->iso8601 : undef
                },
                label => l('End of any employment'),
            },
            endLastEmployment => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('End of any employment'),
            },
            _startEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeStartEmployment($self->base->config('employment_delay') || 0);
                },
                label => l('Start of employment'),
            },
            startEmployment => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('Start of employment'),
            },
            _arrival => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeStartEmployment($self->base->config('employment_delay') || 0, 1, 1);
                },
                label => l('Start of employment'),
            },
            arrival => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('Arrival'),
            },            
            _startStrictEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;
                    $self->_computeStartEmployment();
                },
            },
            startStrictEmployment => {
                formtype => 'DATETIME',
                ro => 1,
            },
            _startCurrentEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $list_empl = $self->base->db->prepare_cached(q{
                        SELECT * FROM employment WHERE "user" = ? and
                            firstday <= now() and
                            (lastday is null or lastday >= now() - '1 days'::interval) 
                            order by firstday asc
                    });
                    $list_empl->execute($self->id);
                    my $start;
                    while (my $res = $list_empl->fetchrow_hashref) {
                        $start = DateTime->from_epoch(epoch => str2time($res->{firstday}));
                        $start->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
                        last;
                    }
                    $list_empl->finish;

                    return $start ? $start->iso8601 : undef
                },
            },
            startCurrentEmployment => {
                formtype => 'DATETIME',
                ro => 1,
            },
            _startFirstEmployment => {
                formtype => 'DATETIME',
                managed => 1,
                ro => 1,
                hide => 1,
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $list_empl = $self->base->db->prepare_cached(q{
                        SELECT * FROM employment WHERE "user" = ?
                        order by firstday asc
                    });
                    $list_empl->execute($self->id);
                    my $res = $list_empl->fetchrow_hashref;
                    $list_empl->finish;
                    my $start;
                    if ($res && $res->{firstday}) {
                        $start = DateTime->from_epoch(epoch => str2time($res->{firstday}));
                        $start->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
                    }
                    return $start ? $start->iso8601 : undef
                },
                label => l('Start of any employment'),
            },
            startFirstEmployment => {
                formtype => 'DATETIME',
                ro => 1,
                label => l('Start of any employment'),
            },
            employmentLength => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $lastday = $self->object->get_attributes('endEmployment') || DateTime->now->ymd('-');
                    my $firstday = $self->object->get_attributes('startEmployment') or return;

                    my @fd = split('-', DateTime->from_epoch(epoch => str2time($firstday))->ymd('-'));
                    my @ld = split('-', DateTime->from_epoch(epoch => str2time($lastday))->ymd('-'));

                    return Date::Calc::Delta_Days(@fd, @ld) +1;
                },
                label => l('Work duration'),
            },
            employmentLengthText => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $firstday = $self->object->get_attributes('startEmployment') or return;
                    my $lastday = $self->object->get_attributes('endEmployment')|| DateTime->now->ymd('-');
                    {
                        my $dtlast = DateTime->from_epoch(epoch => str2time($lastday));
                        $dtlast->add(days => 1);
                        $lastday = $dtlast->ymd('-');
                    }

                    my @fd = split('-', DateTime->from_epoch(epoch => str2time($firstday))->ymd('-'));
                    my @ld = split('-', DateTime->from_epoch(epoch => str2time($lastday))->ymd('-'));

                    my ($Dy,$Dm,$Dd) = Date::Calc::N_Delta_YMD(@fd, @ld);
                    return join(', ',
                        ($Dy ? l('%d years', $Dy)  : ()),
                        ($Dm ? l('%d months', $Dm) : ()),
                        ($Dd ? l('%d days', $Dd)   : ()),
                    );
                },
                label => l('Work duration'),
            },
            cn        => {
                inline => 1, ro => 1,
                get => sub {
                    my ($self) = @_;
                    return join(' ', grep { $_ } 
                        (
                            $self->object->_get_c_field('givenName'),
                            $self->object->_get_c_field('sn')
                        )
                    )
                    || $self->object->_get_c_field('description')
                    || $self->object->id;
                },
            },
            memberOf  => {
                reference => 'group',
                multiple => 1, delayed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        q{
                        select name from "group" join
                        group_attributes on group_attributes.okey = "group".ikey
                        where value = ? and attr = ? and internobject = false
                        } . ($self->base->{wexported} ? '' : ' and "group".exported = true')
                    );
                    $sth->execute($obj->id, 'memberUID');
                    my @res;
                    while (my $res = $sth->fetchrow_hashref) {
                        push(@res, $res->{name});
                    }
                    return \@res;
                },
                set => sub {
                    my ($self, $values) = @_;
                    my %old = map { $_ => 'o' } @{ $self->get };
                    foreach my $group (grep { $_ } ref $values ? @{ $values } : $values) {
                        if ($old{$group}) {
                            $old{$group} = undef; # no change
                        } else {
                            $old{$group} = 'n';
                        }
                    }

                    my $res = 0;
                    foreach my $group (keys %old) {
                        $old{$group} or next; # no change

                        my $ogroup = $self->base->get_object('group', $group) or next;
                        ($ogroup->_get_c_field('sutype') || '') =~ /^(jobtype|contrattype)$/ and next;

                        if ($old{$group} eq 'n') {
                            $res += $ogroup->_addAttributeValue('memberUID', $self->object->id);
                        } else {
                            if (($self->object->_get_c_field('department') || '') eq $ogroup->id) {
                                $self->base->log(LA_WARN,
                                    "Don't removing user %s from group %s: is its department",
                                    $self->object->id, $ogroup->id);
                                next;
                            }
                            $res += $ogroup->_delAttributeValue('memberUID', $self->object->id);
                        }
                    }
                    return $res;
                },
                label => l('Member of'),
            },
            forward => {
                managed => 1,
                multiple => 1,
                get => sub {
                    my ($self) = @_;
                    my $sth = $self->base->db->prepare(q{
                        select forward from aliases where name = ? and internobject = false
                        } . ($self->base->{wexported} ? '' : ' and exported = true'));
                    $sth->execute($self->object->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{forward}
                },
                set => sub {
                    my ($self, $data) = @_;
                    if ($data) {
                        my @datas = ref $data ? @$data : split(/\s*,\s*/, $data);
                        if (my $f = $self->base->get_object('aliases', $self->object->id)) {
                            return $f->_set_c_fields(
                                forward => \@datas,
                                comment => undef,
                                description => 'Forward for user ' . $self->object->id,
                            );
                        } else {
                            if ($self->base->_create_c_object(
                                    'aliases', $self->object->id,
                                    forward => \@datas,
                                    description => 'automatically created for ' . $self->object->id,
                                )) {
                                return 1;
                            } else {
                                $self->base->log(LA_ERR, "Cannot add forward for %s",
                                    $self->object->id);
                            }
                        }
                    } elsif ($self->base->get_object('aliases', $self->object->id)) {
                        if (my $res = $self->base->_delete_object('aliases', $self->object->id)) {
                            return $res;
                        } else {
                            $self->base->log(LA_ERR, "Cannot remove forward for %s",
                                $self->object->id);
                        }
                    } else {
                        return 1;
                    }
                    return;
                },
                label => l('Forward'),
            },
            aliases   => {
                #reference => 'aliases',
                delayed => 1,
                formtype => 'TEXT',
                multiple => 1,
                get => sub {
                    my ($self) = @_;
                    my $sth = $self->base->db->prepare(q{
                        select name from aliases where internobject = false and lower($1) =
                        lower(array_to_string("forward", ','))
                        } . ($self->base->{wexported} ? '' : 'and exported = true'));
                    $sth->execute($self->object->id);
                    my @values;
                    while (my $res = $sth->fetchrow_hashref) {
                        push(@values, $res->{name});
                    }
                    return \@values;
                },
                set => sub {
                    my ($self, $data) = @_;

                    my $res = 0;
                    my %aliases = map { $_ => 1 } grep { $_ } (ref $data ? @{$data} : $data);
                    foreach ($self->object->_get_attributes('aliases')) {
                        $aliases{$_} ||= 0;
                        $aliases{$_} +=2;
                    }
                    foreach (keys %aliases) {
                        if ($aliases{$_} == 2) {
                            if ($self->base->_delete_object('aliases', $_)) {
                                $res++
                            } else {
                                $self->base->log(LA_ERR,
                                    "Cannot remove aliases %s from user %s", $_,
                                    $self->object->id);
                            }
                        } elsif ($aliases{$_} == 1) {
                            if ($self->base->_create_c_object(
                                    'aliases', $_,
                                    forward => [ $self->object->id ],
                                    description => 'automatically created for ' . $self->object->id,
                                )) {
                                $res++
                            } else {
                                $self->base->log(LA_ERR, 'Cannot set forward %s to user %s',
                                    $_, $self->object->id);
                                return;
                            }
                        } # 3 no change
                    }
                    $res
                },
                label => l('Aliases'),
            },
            revaliases => {
                formtype => 'TEXT',
                get => sub {
                    my ($self) = @_;

                    if (my $obj = $self->base->
                        get_object('revaliases', $self->object->id)) {
                        return $obj->get_attributes('as');
                    } else {
                        return;
                    }
                },
                set => sub {
                    my ($self, $data) = @_;
                
                    my $res = 0;
                    if ($data) {
                        if (my $obj = $self->base->
                            get_object('revaliases', $self->object->id)) {
                            my $ares = $obj->set_c_fields(
                                'as' => $data,
                                'exported' => ($self->object->get_attributes('exported') || 0),
                            );
                            if (defined($ares)) {
                                $res+=$ares;
                            } else {
                                $self->base->log(LA_ERR, 'Cannot set revaliases for user %s',
                                    $self->object->id);
                            }
                        } else {
                            if ($self->base->_create_c_object(
                                    'revaliases',
                                    $self->object->id, as => $data,
                                    'exported' => ($self->object->get_attributes('exported') || 0),
                                    description => 'automatically created for ' . $self->object->id,
                                )) {
                                $res++;
                            } else {
                                $self->base->log(LA_ERR, 'Cannot set revaliases for user %s',
                                    $self->object->id);
                            }
                        }
                    } else {
                        $self->base->_delete_object('revaliases', $self->object->id);
                        $res++;
                    }

                    $res
                },
            },
            manager => {
                reference => 'user',
                ro => 1,
                get => sub {
                    my ($self) = @_;
                    if (my $manager = $self->object->_get_c_field('managerContact')) {
                        return $manager;
                    } elsif (my $department = $self->object->_get_c_field('department')) {
                        my $obj = $self->base->get_object('group', $department);
                        return $obj->_get_c_field('managedBy');
                    } else {
                        return;
                    }
                },
                label => l('Responsible'),
            },
            department => {
                reference => 'group',
                can_values => sub {
                    $base->search_objects('group', 'sutype=dpmt')
                },
                monitored => 1,
                label => l('Department'),
            },
            contratType => {
                reference => 'group',
                can_values => sub {
                    $base->search_objects('group', 'sutype=contrattype')
                },
                monitored => 1,
                label => l('Type of contract'),
            },
            site => {
                reference => 'site',
                can_values => sub {
                    $base->search_objects('site')
                },
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Site'),
            },
            co => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
            },
            l => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('City'),
            },
            postalCode => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Postal code'),
            },
            streetAddress => {
                formtype => 'TEXTAREA',
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Street'),
            },
            postOfficeBox => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Post office box'),
            },
            st => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
            },
            facsimileTelephoneNumber => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Fax number'),
            },
            o => {
                ro => 1,
                iname => 'company',
                label => l('Company'),
            },
            ou => {
                iname => 'department',
                ro => 1,
                label => l('Department'),
            },
            telephoneNumber => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Phone number'),
            },
            physicalDeliveryOfficeName => {
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                set => $subsetaddress,
                label => l('Office'),
            },
            uid => {
                iname => 'name',
                ro => 1,
                label => l('Login'),
            },
            cn =>  { iname => 'name', ro => 1 },
            gecos => {
                managed => 1,
                ro => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;

                    my $ec = $obj->_get_c_field('endcircuit') || '';

                    my $date = $ec
                        ? sprintf('%s (%s)', $obj->_get_c_field('expireTextEC'), $obj->_get_c_field('expireText'))
                        : $obj->_get_c_field('expireText');

                    $date ||= '';

                    my $gecos = sprintf("%s,%s,%s,%s",
                        join(' ', grep { $_ }
                                ($obj->_get_c_field('givenName'),
                                ($obj->_get_c_field('sn'))))
                            || $obj->_get_c_field('description') || '',
                        join(' - ', grep { $_ } (($obj->_get_c_field('site') ||
                                    $obj->_get_c_field('l')),
                            $obj->_get_c_field('physicalDeliveryOfficeName'))) || '',
                        $obj->_get_c_field('telephoneNumber') || '',
                        $date,
                    );
                    $gecos =~ s/:/ /g;
                    return to_ascii($gecos);
                },
                label => l('GECOS'),
            },
            displayName  => {
                ro => 1, managed => 1,
                get => sub {
                    my ($self) = @_;
                    return join(' ', grep { $_ } 
                        (
                            $self->object->_get_c_field('givenName'),
                            $self->object->_get_c_field('sn')
                        )
                    )
                    || $self->object->_get_c_field('description')
                    || $self->object->id;
                },
                label => l('Name'),
            },
            sAMAccountName  => {
                ro => 1,
                managed => 1,
                iname => 'name',
                get => sub {
                    my ($self) = @_;
                    substr($self->object->id, 0, 19)
                },
            },
            accountExpires => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        sprintf(
                            q{select extract(epoch from COALESCE(endcircuit,  expire)) + 11644474161 as expire
                            from %s where %s = ?},
                            $obj->db->quote_identifier($obj->_object_table),
                            $obj->db->quote_identifier($obj->_key_field),
                        )
                    );
                    $sth->execute($obj->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{expire} ? sprintf("%.f", $res->{expire} * 1E7) : '9223372036854775807';
                }
            },
            shadowExpire => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        sprintf(
                            q{select justify_hours(COALESCE(endcircuit,  expire) - '1/1/1970'::timestamp) as expire
                            from %s where %s = ?},
                            $obj->db->quote_identifier($obj->_object_table),
                            $obj->db->quote_identifier($obj->_key_field),
                        )
                    );
                    $sth->execute($obj->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return -1 unless($res->{expire});
                    $res->{expire} =~ /(\d+) days\s*(\w)?/;
                    # Add one day is time is not 00H00
                    return $1 + ($2 ? 1 : 0);
                }
            },
            directReports => {
                reference => 'user',
                ro => 1,
                delayed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        q{
                        SELECT
                        "user".name FROM
                        public."user",
                        public.user_attributes_groups,
                        public.group_attributes_users,
                        public.group_attributes_base gb,
                        public."group"
                        WHERE
                        "user".ikey = user_attributes_groups.okey AND
                        user_attributes_groups.value = "group".name AND
                        group_attributes_users.okey = gb.okey AND
                        "group".ikey = group_attributes_users.okey AND
                        gb.attr = 'sutype' AND
                        gb.value = 'dpmt' AND
                        group_attributes_users.attr = 'managedBy' AND
                        group_attributes_users.value = ?
                        } . ($self->base->{wexported} ? '' : ' and "user".exported = true') . q{
                            and "user".ikey not in
                        (select okey from user_attributes_users
                        where attr = 'manager' and value != ? )
                        union
                        select "user".name FROM public."user",
                        user_attributes_users where
                        user_attributes_users.attr = 'manager' and user_attributes_users.value = ?
                            and "user".ikey = user_attributes_users.okey
                        } . ($self->base->{wexported} ? '' : ' and "user".exported = true')
                    );
                    $sth->execute($obj->id, $obj->id, $obj->id);
                    my @res;
                    while (my $res = $sth->fetchrow_hashref) {
                        push(@res, $res->{name});
                    }
                    return \@res;
                },
            },
            managedObjects => { ro => 1, reference => 'group', },
            otheraddress => {
                ro => 1,
                reference => 'address',
                get => sub {
                    my ($self) = @_;
                    my $sth = $self->base->db->prepare_cached(q{
                        select name from address left join address_attributes
                        on address.ikey = address_attributes.okey and
                        address_attributes.attr = 'isMainAddress'
                        where "user" = ?
                        order by address_attributes.attr
                        } . ($self->base->{wexported} ? '' : ' and "address".exported = true'));
                    $sth->execute($self->object->id);
                    my @values;
                    while (my $res = $sth->fetchrow_hashref) {
                        push(@values, $res->{name});
                    }
                    return \@values;
                },
            },
            mainaddress => {
                ro => 1,
                reference => 'address',
                get => sub {
                    my ($self) = @_;
                    my $sth = $self->base->db->prepare_cached(q{
                        select name from address join address_attributes on ikey = okey
                        where "user" = ? and attr = 'isMainAddress'
                        } . ($self->base->{wexported} ? '' : ' and "address".exported = true'));
                    $sth->execute($self->object->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{name};
                },
            },
            postalAddress => {
                ro => 1,
                get => sub {
                    my ($self) = @_;
                    if (my $fmainaddress = $self->object->_get_c_field('mainaddress')) {
                        $self->base->get_object('address', $fmainaddress)
                            ->_get_c_field($self->name);
                    } else {
                        return;
                    }
                },
                label => l('Postal Address'),
            },
            facsimileTelephoneNumber => {
                ro => 1,
                label => l('Fax number'),
            },
            allsite   => {
                ro => 1,
                reference => 'site',
            },
            managerContact => {
                delayed => 1,
                can_values => sub {
                    my %uniq = map { $_ => 1 } grep { $_ }
                    (($_[1] ? $_[1]->get_attributes('managerContact') : ()),
                    $base->search_objects('user', 'active=1'));
                    sort keys %uniq;
                },
                reference => 'user',
                monitored => 1,
                iname => 'manager',
                label => l('Manager'),
            },
            expireTextEC => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        sprintf(
                            q{select to_char(COALESCE(endcircuit,  expire), 'YYYY/MM/DD') as expire
                            from %s where %s = ?},
                            $obj->db->quote_identifier($obj->_object_table),
                            $obj->db->quote_identifier($obj->_key_field),
                        )
                    );
                    $sth->execute($obj->id) or $obj->db->rollback;
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{expire}
                },
            },
            expireText => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    my $sth = $obj->db->prepare_cached(
                        sprintf(
                            q{select to_char(expire, 'YYYY/MM/DD') as expire
                            from %s where %s = ?},
                            $obj->db->quote_identifier($obj->_object_table),
                            $obj->db->quote_identifier($obj->_key_field),
                        )
                    );
                    $sth->execute($obj->id) or $obj->db->rollback;
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{expire}
                },
            },
            krb5ValidEnd => {
                ro => 1,
                managed => 1,
                get => sub {
                    my ($self) = @_;
                    my $sth = $self->object->db->prepare_cached(
                        sprintf(
                            q{select date_part('epoch', COALESCE(endcircuit,  expire))::int as expire
                            from %s where %s = ?},
                            $self->object->db->quote_identifier($self->object->_object_table),
                            $self->object->db->quote_identifier($self->object->_key_field),
                        )
                    );
                    $sth->execute($self->object->id) or $self->object->db->rollback;
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    return $res->{expire}
                },
            },
            cells  => {
                ro => 1,
                reference => 'group',
            },
            departments => {
                reference => 'group',
                delayed => 1,
                ro => 1,
                label => l('Departments'),
            },
            arrivalDate => { },
            expired => {
                ro => 1,
                label => l('Expired'),
            },
            active => {
                ro => 1,
                label => l('Active'),
            },
            status => {
                ro => 1,
                label => l('Statut du compte'),
            },
            pwdAccountLockedTime => {
                managed => 1,
                ro => 1,
                get => sub {
                    my ($self) = @_;
                    my $obj = $self->object;
                    if ($obj->_get_c_field('locked')) {
                        return '000001010000Z';
                    } else {
                        my $sth = $obj->db->prepare_cached(
                            sprintf(
                                q{select to_char(COALESCE(endcircuit,  expire) AT TIME ZONE 'Z', 'YYYYMMDDHH24MISSZ') as expire
                                from %s where %s = ? and expire < now()},
                                $obj->db->quote_identifier($obj->_object_table),
                                $obj->db->quote_identifier($obj->_key_field),
                            )
                        );
                        $sth->execute($obj->id);
                        my $res = $sth->fetchrow_hashref;
                        $sth->finish;
                        return $res->{expire}
                    }
                },
            },
            userPassword => { readable => 0, },
            wWWHomePage => {
                label => l('Web Page'),
                formopts => { length => 35 },
            },
            title => { },
            snNative => {
                label => l('Native name'),
            },
            givenNameNative => {
                label => l('Native first name'),
            },
            sn => {
                label => l('Name'),
            },
            shadowWarning => { },
            shadowMin => { },
            shadowMax => { },
            shadowLastChange => { },
            shadowInactive => { },
            shadowFlag => { },
            otherTelephone => { },
            nickname => {
                label => l('Nickname'),
            },
            mobile => { },
            mail => {
                label => l('Email'),
            },
            otherEmail => {
                label => l('External mail'),
            },
            labeledURI => { },
            jobType => { },
            ipPhone => { },
            initials => {
                label => l('Initials'),
                checkinput => sub {
                    $_[0] or return;
                    return(length($_[0]) <= 6)
                }
            },
            homePhone => { },
            homeDirectory => {
                label => l('Home directory'),
            },
            halReference => {
                label => l('HAL id'),
            },
            grade => { },
            givenName => {
                label => l('First name'),
            },
            encryptedPassword => { },
            description => {
                label => l('Description'),
            },
            company => {
                label => l('Company'),
            },
            employer => {
                label => l('Employer'),
            },
            comment => {
                label => l('Comment'),
            },
            college => { },
            passwordLastSet => {
                ro => 1,
                label => l('Password set'),
            },
            oldPassword => {
                multiple => 1,
            },
            bannedPassword => {
                multiple => 1,
            },
            sshPublicKey => {
                multiple => 1,
                formopts => { length => 45 },
            },
            currentEmployment => {
                managed => 1,
                ro => 1,
                reference => 'employment',
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $now = DateTime->now()->iso8601 . 'Z';

                    my $sth = $self->base->db->prepare_cached(
                        q{
                        select name from employment where firstday <= ?::timestamp and
                        (lastday is null or lastday >= ?::timestamp - '1 days'::interval) and "user" = ?
                        limit 1
                        }
                    );
                    $sth->execute($now, $now, $self->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    if ($res) {
                        return $res->{name}
                    } else {
                        return;
                    }
                },
            },
            nextEmployment => {
                managed => 1,
                ro => 1,
                reference => 'employment',
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $now = DateTime->now()->iso8601 . 'Z';

                    my $sth = $self->base->db->prepare_cached(
                        q{
                        select name from employment where firstday > ?::timestamp and "user" = ?
                        order by firstday asc
                        limit 1
                        }
                    );
                    $sth->execute($now, $self->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    if ($res) {
                        return $res->{name}
                    } else {
                        return;
                    }
                }
            },
            prevEmployment => {
                managed => 1,
                ro => 1,
                reference => 'employment',
                get => sub {
                    my ($attr) = @_;
                    my $self = $attr->object;

                    my $now = DateTime->now()->iso8601 . 'Z';

                    my $sth = $self->base->db->prepare_cached(
                        q{
                        select name from employment where
                        (lastday is not null and lastday <= ?::timestamp - '1 days'::interval) and "user" = ?
                        order by firstday desc
                        limit 1
                        }
                    );
                    $sth->execute($now, $self->id);
                    my $res = $sth->fetchrow_hashref;
                    $sth->finish;
                    if ($res) {
                        return $res->{name}
                    } else {
                        return;
                    }
                }
            },
            appliedEmployement => {
                hide => 1,
                reference => 'employment',
            },
            contratTypeHistory => {
                reference => 'group',
                can_values => sub {
                    $base->search_objects('group', 'sutype=contrattype')
                },
                multiple => 1,
            },
            employmentHistory => {
                reference => 'employment',
                multiple => 1,
                ro => 1,
            },
            hosted => {
                formtype => 'CHECKBOX',
                label => l('Hosted'),
            },
            createRequestId => {
                label => l('Account Request id')
            },
            requestId => {
                label => l('Request id')
            }

    };

    my $employmentro = sub {
        my $setting = $base->config('employment_lock_user') || 'any';

        for ($setting) {
            /^always$/ and return 1;
            /^never$/ and return 0;

            $_[0] or return 0;

            /^any$/i and return $_[0]->listEmployment ? 1 : 0;
            /^active/i and do {
                return $_[0]->_get_c_field('currentEmployment')
                ? 1
                : $_[0]->_get_c_field('nextEmployment') ? 1 : 0;
            };
            /(\S+)=(\S+)/ and do {
                my $attr = $_[0]->_get_c_field($1);
                if (defined($attr)) {
                    if ($2 eq '*') {
                        return 1;
                    } elsif($2 eq $attr) {
                        return 1;
                    } else {
                        return 0;
                    }
                } else {
                    return 0;
                }
            };
        }
        return $_[0]->listEmployment ? 1 : 0; # default is any!
    };

    foreach (qw(contratType managerContact company endcircuit department hosted requestId
                contratTypeHistory employer)) {
        $attrs->{$_}{ro} = $employmentro;
    }

    $attrs->{expire}{ro} = sub {
        my $expireOn = $base->config('expireOn') || '';
        if ($expireOn eq 'never') {
            return 0;
        }

        $employmentro->($_[0]);
    };

    $class->SUPER::_get_attr_schema($base, $attrs)
}

sub CreateAlias {
    my ($class, $base, $name, $for) = @_;

    my $stAddAlias = $base->db->prepare_cached(
        q{INSERT INTO "user" (name, uidnumber,            gidnumber, oalias, oaliascache) values
                             (?,    -nextval('ikey_seq'), ?,         ?,      ?)}
    );

    my $ref = $base->_derefObject($class->type, $for);
    my $res = $stAddAlias->execute($name, -1, $for, $ref ? $ref->id : undef);
    return $res ? 1 : 0;
}

sub _get_state {
    my ($self, $state) = @_;
    for ($state) {
        /^expired$/ and do {
            my $attribute = $self->attribute('expire');
            $attribute->check_acl('r') or return;
            my $sth = $self->db->prepare_cached(
                q{ select coalesce(expire < now(), false) as exp from "user" 
                where "user".name = ?}
            );
            $sth->execute($self->id);
            my $res = $sth->fetchrow_hashref;
            $sth->finish;
            return $res->{exp} ? 1 : 0;
        };
    }
}

sub set_fields {
    my ($self, %data) = @_;

    my $old;
    if (exists($data{department})) {
        $old = $self->_get_attributes('department');
    }

    if (($data{department} || '') eq ($old || '')) {
        # We do not remove the group, there is no change
        $old = undef;
    }

    if ($old) {
        # If the department is no longer a department
        # we do nothing
        my @names = $self->base->search_objects('group', 'name=' . $old, 'sutype=dpmt');
        if (! @names) {
            $old = undef;
        }
    }

    my $res = $self->SUPER::set_fields(%data) or return;

    if ($self->base->config('remove_old_dpmt') && $old) {
        $self->base->log(LA_DEBUG,
            "Removing %s from group %s (department change to %s)",
            $self->id,
            $old,
            $data{department} || '');
        $self->_delAttributeValue('memberOf', $old) or return;
        $res++;
    }

    $res
}

=head2 listEmployment

Return the ordered list of contract

=cut

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

    my $sth = $self->base->db->prepare_cached(
        q{
        select name from employment where "user" = ?
        order by lastday desc NULLS first
        }
    );
    $sth->execute($self->id);
    my @list = ();
    while (my $res = $sth->fetchrow_hashref) {
        push(@list, $res->{name});
    }

    @list
}

sub _reported_atributes { qw(contratType endcircuit hosted requestId company employer) }

=head2 applyCurrentEmployment

Search the current employment is any and apply paramter to user

=cut

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

    # Get current employment name
    my $currentempl = $self->get_attributes('currentEmployment') || '';

    $self->base->log(
        LA_DEBUG,
        "Applying Employement %s to user %s",
        $currentempl || '(none)',
        $self->id
    );

    if (my $currentemployment = $self->base->get_object('employment', $currentempl)) {

        # If an employement apply we set the value to the user object

        $self->computeEmploymentDate;

        my %attrsets = (
            appliedEmployement => $currentemployment->id,
        );
        foreach my $attr (_reported_atributes(), qw(department managerContact)) {
            my $uval = $self->get_attributes($attr) || '';
            my $cval = $currentemployment->get_attributes($attr) || '';

            if ($attr eq 'managerContact') {
                if (!$cval) {
                    my $dpmt  = $currentemployment->get_attributes('department') or last;
                    my $odmpt = $currentemployment->base->get_object('group', $dpmt) or last;
                    $cval = $odmpt->get_attributes('managedBy');
                }
            }

            if ($uval ne $cval) {
                my $oattr = $currentemployment->base->attribute('user', $attr);
                $attrsets{$oattr->iname} = $cval;
            }
        }

        if (keys %attrsets) {
            if (my $res = $self->set_fields(%attrsets)) {
                $self->ReportChange('Update', 'Attr %s updated to match Employment %s', join(', ', sort keys %attrsets), $currentemployment->id);
                return $res;
            }
        } else {
            return 1;
        }
    } else {
        # No current employment, resetting values:

        return $self->_resetEmployment;
    }

}

# Reset attribute value set by employment
# except managerContact and expire

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

    $self->computeEmploymentDate;

    my %changes = (
        appliedEmployement => undef,
    );

    my @attributesToReset = (_reported_atributes, qw(department));

    foreach my $attr (@attributesToReset) {
        my $default = $self->base->config("unemployment.$attr") || '';
        my $old = $self->_get_attributes($attr) || '';

        if ($old ne $default) {
            $changes{$attr} = $default || undef;
        }
    }

    if(!$self->get_attributes('managerContact')) {
        if (my $next = $self->_get_attributes('nextEmployment')) {
            my $onext = $self->base->get_object('employment', $next);
            $changes{'manager'} = $onext->_get_attributes('managerContact');
        }
    }

    if (%changes) {
        if ($self->set_fields(%changes)) {
            $self->base->log(LA_NOTICE, "Updating user %s to match unemployment", $self->id);
            $self->ReportChange('Update', 'Update %s to match unemployment', join(', ', sort keys %changes));
            return 1;
        } else {
            return 0;
        }
    } else {
        return 1;
    }

}


=head2 computeEmploymentDate

Compute and copy to user start and end employment date

=cut

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

    my $currentemployment = $self->get_attributes('currentEmployment') || '';

    my $expire = str2time($self->_get_attributes('expire') || '1970-01-01T00:00:00');

    my %changes;
    my @employmentDate = qw(
        endEmployment   endStrictEmployment   endCurrentEmployment   endLastEmployment
        startEmployment startStrictEmployment startCurrentEmployment startFirstEmployment
        arrival departure
    );
    foreach (@employmentDate) {
        my $old = $self->_get_attributes($_) || '';
        my $new = $self->_get_attributes("_$_") || '';
        if ($old ne $new) {
            $changes{$_} = $new || undef;
        }
    }

    # If there is no current employment we try to find any to not let expire
    # unset

    my $expireOn = $self->base->config('expireOn') || '';
    if (!grep { $_ eq $expireOn } (@employmentDate, '', 'never')) {
        $self->base->log(LA_ERR, "expireOn set to invalid parameter %s, using endEmployment instead", $expireOn);
        $expireOn = undef;
    }
    $expireOn ||= 'endEmployment';

    # We check if matching start exists to know if using end* for expiration is
    # safe, even undef
    my %end2start = (
        endEmployment        => 'startEmployment',
        endStrictEmployment  => 'startStrictEmployment',
        endCurrentEmployment => 'startCurrentEmployment',
        endLastEmployment    => 'startFirstEmployment',
    );

    # TODO rework this, working code but bloat
    if ($expireOn ne 'never') {
        my $endemploy = '';
        if ($self->_get_attributes("_$end2start{$expireOn}")) {
            $endemploy = $self->_get_attributes("_$expireOn") || '';
            $endemploy ||= 'UNCHANGED' unless($currentemployment);
        } elsif (($self->base->config('unemployed_expire') ||'') eq 'no') {
            $endemploy = '';
        } else {
            # No expiration date apply, don't touch
            $endemploy = 'UNCHANGED';
        }

        if ($endemploy ne 'UNCHANGED') {
            my $nextexpire = str2time( $endemploy || '1970-01-01T00:00:00' );

            if ($expire != $nextexpire) {
                $changes{expire} = $endemploy;
            }
        }
    }

    if (keys %changes) {
        $self->base->log(LA_DEBUG, 'Applying employment state to user %s for field %s', $self->id, join(', ', keys %changes));
        $self->ReportChange('Update', 'Update %s to match employment', join(', ', sort keys %changes));
        if (exists($changes{expire})) {
            $self->ReportChange('Update', 'Expire update to %s to match employment', ($changes{expire} || '(none)'));
            $self->base->log(LA_DEBUG, 'New expiration is %s for user %s', ($changes{expire} || '(none)'), $self->id);
        }
        $self->set_fields(%changes);
    } else {
        $self->base->log(LA_DEBUG, 'No employment change for user %s', $self->id);
    }

    $self->_computeEmploymentHistory();

    return 1;
}

sub _computeStartEmployment {
    my ($self, $delay, $any, $workday) = @_;

    $delay ||= 0;
    my $start;
    my $nstart;

    if (my $next = $self->_get_attributes('nextEmployment')) {
        my $onext = $self->base->get_object('employment', $next);
        $nstart = DateTime->from_epoch(epoch => str2time($onext->_get_attributes('firstday')));
        $nstart->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
    }

    my $list_empl = $self->base->db->prepare_cached(q{
        SELECT *, (lastday is null or lastday >= now() - '1days'::interval) as "current" FROM employment
        WHERE "user" = ? and firstday < now()
        order by firstday desc
        });
    $list_empl->execute($self->id);

    while (my $res = $list_empl->fetchrow_hashref) {
        if ($res->{current}) {
        } elsif ($nstart) {
            my $prevend = DateTime->from_epoch(epoch => str2time($res->{lastday}));
            $prevend->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
            my $tstart = $nstart->clone;
            $tstart->subtract(days => $delay + 1);
            if ($tstart->ymd gt $prevend->ymd) {
                last;
            }
        } elsif ((!$res->{current}) && (!$any)) {
            last;
        }
        $nstart = DateTime->from_epoch(epoch => str2time($res->{firstday}));
        $nstart->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
        $start = $nstart->clone;
    }
    $list_empl->finish;

    $start ||= $nstart if ($any);

    if ($start) {
        if ($workday) {
            my $day_of_week = $start->day_of_week;
            $start->add(days =>
                $day_of_week == 6 ? 2 :
                $day_of_week == 7 ? 1 : 0
            );
        }
    }

    return $start ? $start->iso8601 : undef
}

sub _computeEndEmployment {
    my ($self, $delay, $any, $workday) = @_;

    $delay ||= 0;
    my $end;
    my $pend;

    if (my $prev = $self->_get_attributes('prevEmployment')) {
        my $oprev = $self->base->get_object('employment', $prev);
        $pend = DateTime->from_epoch(epoch => str2time($oprev->_get_attributes('lastday')));
        $pend->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
        $pend->add(hours => 23, minutes => 59, seconds => 59);
    }

    my $list_empl = $self->base->db->prepare_cached(q{
        SELECT *, firstday <= now() as "current" FROM employment WHERE "user" = ? and
        (lastday is null or lastday >= now() - '1 days'::interval)
        order by firstday asc
        });
    $list_empl->execute($self->id);
    while (my $res = $list_empl->fetchrow_hashref) {
        if (!$res->{lastday}) {
            # Ultimate employment.
            $list_empl->finish;
            return undef;
        }
        if ($res->{current}) {
        } elsif ($end) {
            my $nextstart = DateTime->from_epoch(epoch => str2time($res->{firstday}));
            $nextstart->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
            my $tend = $end->clone;
            $tend->add(days => $delay + 1);
            if ($tend->ymd lt $nextstart->ymd) {
                last;
            }
        } elsif ($pend) {
            my $nextstart = DateTime->from_epoch(epoch => str2time($res->{firstday}));
            $nextstart->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
            my $tend = $pend->clone;
            $tend->add(days => $delay + 1);
            if ($tend->ymd lt $nextstart->ymd) {
                last;
            }
            $end = $tend
        } elsif ((!$res->{current}) && (!$any)) {
            last;
        }
        $end = DateTime->from_epoch(epoch => str2time($res->{lastday}));
        $end->set_time_zone( DateTime::TimeZone->new( name => 'local' ) );
        $end->add(hours => 23, minutes => 59, seconds => 59);
    }
    $list_empl->finish;

    $end ||= $pend if($any);

    if ($end) {
        if ($workday) {
            my $day_of_week = $end->day_of_week;
            $end->subtract(days =>
                $day_of_week == 7 ? 2 :
                $day_of_week == 6 ? 1 : 0
            );
        }
    }

    return $end ? $end->iso8601 : undef
}

# Compute a summary of employement and store the value
# into an attribute

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

    my %changes;

    {
        my $sth = $self->db->prepare_cached(q{
            select employment.name from employment
            where "user" = ? and employment.firstday < now()
        });
        $sth->execute($self->id);
        my @values;
        while (my $res = $sth->fetchrow_hashref) {
            push(@values, $res->{name});
        }
        $changes{"employmentHistory"} = \@values;
    }

    foreach my $attribute (qw(contratType)) {
        my $sth = $self->db->prepare_cached(q{
            select employment_attributes.value from employment
            join employment_attributes
            on employment.ikey = employment_attributes.okey
            where "user" = ? and employment_attributes.attr = ?
            and employment.firstday < now()
            group by employment_attributes.value
            });
        $sth->execute($self->id, $attribute);
        my @values;
        while (my $res = $sth->fetchrow_hashref) {
            push(@values, $res->{value});
        }
        $changes{$attribute . "History"} = \@values;
    }

    $self->set_fields(%changes);
}

=head2 storeBannedPassword($epassword)

Add an encrypted password to untrust list

=cut

sub storeBannedPassword {
    my ($self, $EncPass) = @_;

    my @banned = sort { $b cmp $a } $self->_get_attributes('bannedPassword');
    my $now = DateTime->now; 
    unshift(@banned, $now->iso8601 . ';' . $EncPass);
    $self->set_fields('bannedPassword', [ grep { $_ } @banned ]);

}

=head2 banCurrentPassword

Store the current password as banned

=cut

sub banCurrentPassword {
    my ($self) = @_;
    
    my $old = $self->get_field('userPassword') or return;
    $self->storeBannedPassword($old);
}

sub check_password {
    my ( $self, $password ) = @_;

    my $res = $self->SUPER::check_password($password);
    if ($res !~ /^ok$/) {
        return $res;
    }

    foreach my $banned ($self->_get_attributes('bannedPassword')) {
        my ($date, $oldPassword) = $banned =~ /^([^;]*);(.*)/;
        if (crypt($password, $oldPassword) eq $oldPassword) {
            return "Banned password, cannot be used anymore";
        }
    }

    return 'ok';
}

sub _set_password {
    my ($self, $clear_pass) = @_;
    if (my $attr = $self->base->attribute($self->type, 'userPassword')) {
        my $field = $attr->iname;

        # Storing as old password
        my @olds = sort { $b cmp $a } $self->_get_attributes('oldPassword');
        if (my $old = $self->get_field('userPassword')) {
            my $now = DateTime->now; 
            unshift(@olds, $now->iso8601 . ';' . $old);
            $self->set_fields('oldPassword', [ grep { $_ } @olds[0 .. 14] ]);
        }

        my $res = $self->set_fields($field, $self->base->passCrypt($clear_pass));
        if ($res) {
            if ($self->base->get_global_value('rsa_public_key')) {
                $self->setCryptPassword($clear_pass) or return;
            }
        }

        $self->set_fields('passwordLastSet', DateTime->now->datetime);
        $self->base->log(LA_NOTICE,
            'Mot de passe changé pour %s',
            $self->id
        );


        return $res;
    } else {
        $self->log(LA_WARN,
            "Cannot set password: userPassword attributes is unsupported");
    }
}

=head2 setCryptPassword($clear_pass)

Store password encrypted using RSA encryption.

=cut

sub setCryptPassword {
    my ($self, $clear_pass) = @_;
    if (my $serialize = $self->base->get_global_value('rsa_public_key')) {
        my $public = Crypt::RSA::Key::Public->new;
        $public = $public->deserialize(String => [ $serialize ]);
        my $rsa = new Crypt::RSA ES => 'PKCS1v15';
        my $rsa_password = $rsa->encrypt (
            Message    => $clear_pass,
            Key        => $public,
            Armour     => 1,
        ) || die $rsa->errstr();
        if (!$self->_set_c_fields('encryptedPassword', $rsa_password)) {
            $self->log(LA_ERR,
                "Cannot set 'encryptedPassword' attribute for object %s/%s",
                $self->type, $self->id,
            );
            return;
        }
    }
    $self->ReportChange('Password', 'Password stored using internal key');
    return 1;
}

=head2 _InjectCryptPasswd($cryptpasswd)

Inject a password encrypted using standard UNIX method.

The passwrod will be used to authenticate user inside the application but it
will not be transmit to any other database.

=cut

sub _InjectCryptPasswd {
    my ($self, $cryptpasswd) = @_;

    if (my $current = $self->get_field('userPassword')) {
        if ($cryptpasswd eq $current) {
            return 1;
        }
    }
    my $res = $self->set_fields('userPassword', $cryptpasswd);

    if ($res) {
        $self->base->log(LA_NOTICE, 'Crypted password injected for %s', $self->id);
        return 1;
    } else {
        $self->base->log(LA_ERR, 'Cannot inject crypted password for %s', $self->id);
        return 0;
    }
}

=head2 GenPasswordResetId

Return a new id allowing passowrd reset

=cut

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

    my $id = LATMOS::Accounts::Utils::genpassword(length => 32);

    my $sth = $self->base->db->prepare_cached(q{
        INSERT INTO passwordreset (id, "user") values (?,?)
    });

    if ($sth->execute($id, $self->id)) {
        return $id;
    } else {
        return;
    }
}

=head2 SendPasswordReset($url)

Generate a password reset Id and the to the user.

C<$url> is the URL where the password can changed (printf forward, the %s is
replaced by the request id)

=cut

sub SendPasswordReset {
    my ($self, $url) = @_;

    my $id = $self->GenPasswordResetId;

    my $mail = $self->_get_attributes('mail') or do {
        $self->base->log(LA_ERR, "Cannot sent reset password mail: no mail found");
        return;
    };

    my $MailSubject = $self->base->la->val('_default_', 'mailSubject', 'LATMOS::Accounts');

    my %mail = (
        Subject => "$MailSubject: pasword reset",
        'X-LATMOS-Reason' => 'Password Reset',
        to => $mail,
    );

    if (my $otherEmail = $self->_get_attributes('otherEmail')) {
        $mail{cc} = $otherEmail;
    }

    my $vars = {
        url => sprintf($url, $id),
    };
    $vars->{id} =  $id;
    $vars->{obj} = $self;

    my $lamail = LATMOS::Accounts::Mail->new(
        $self->base->la,
        'passwordreset.mail',
    );

    if ($lamail->process(\%mail, $vars)) {
        $self->base->log(LA_NOTICE,
            "Reset password sent to %s for user %s",
            $mail{to},
            $self->id,
        );
        return 1;
    } else {
        return;
    }

}

=head2 CheckPasswordResetId($id)

Return True if the reset password ID can be found and is less than one day old

=cut

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

    my $sth = $self->base->db->prepare_cached(q{
        SELECT * FROM passwordreset WHERE
            "user" = ? and
            id = ? and
            "create" >= now() - '1 days'::interval
    });
    $sth->execute($self->id, $id);

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

    return $res ? 1 : 0;
}

=head2 DeletePasswordId($id) 

Delete password reset C<$id> and all expired request

=cut

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

    my $sth = $self->base->db->prepare_cached(q{
        DELETE FROM passwordreset WHERE
        "user" = ? AND (id = ? or "create" < now() - '1 days'::interval)
    });

    $sth->execute($self->id, $id);
}

1;

__END__

=head1 SEE ALSO

=head1 AUTHOR

Olivier Thauvin, E<lt>olivier.thauvin@latmos.ipsl.frE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2008, 2009 CNRS SA/CETP/LATMOS

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.10.0 or,
at your option, any later version of Perl 5 you may have available.

=cut