path: root/LedgerSMB/HR.pm

#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2003
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has NOT undergone whitespace cleanup.
#
#======================================================================
#
# backend code for human resources and payroll
#
#======================================================================

package HR;

sub get_employee {
    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

    my $query;
    my $sth;
    my $ref;
    my $notid = "";

    if ( $form->{id} ) {
        $query = qq|SELECT e.* FROM employees e WHERE e.id = ?|;
        $sth   = $dbh->prepare($query);
        $sth->execute( $form->{id} )
          || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

        $ref = $sth->fetchrow_hashref(NAME_lc);

        # check if employee can be deleted, orphaned
        $form->{status} = "orphaned" unless $ref->{login};

        $ref->{employeelogin} = $ref->{login};
        delete $ref->{login};
        for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

        $sth->finish;

        # get manager
        $form->{managerid} *= 1;

        $sth = $dbh->prepare("SELECT name FROM employees WHERE id = ?");
        $sth->execute( $form->{managerid} );
        ( $form->{manager} ) = $sth->fetchrow_array;

        $notid = qq|AND id != | . $dbh->quote( $form->{id} );

    }
    else {

        ( $form->{startdate} ) = $dbh->selectrow_array("SELECT current_date");

    }

    # get managers
    $query = qq|
          SELECT id, name
            FROM employees
           WHERE sales = '1'
                 AND role = 'manager'
                 $notid
        ORDER BY 2|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_manager} }, $ref;
    }
    $sth->finish;

    $dbh->commit;

}

sub save_employee {
    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};
    my $query;
    my $sth;

    if ( !$form->{id} ) {
        my $uid = localtime;
        $uid .= "$$";

        $query = qq|INSERT INTO employees (name) VALUES ('$uid')|;
        $dbh->do($query)
          || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

        $query = qq|SELECT id FROM employees WHERE name = '$uid'|;
        $sth   = $dbh->prepare($query);
        $sth->execute
          || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

        ( $form->{id} ) = $sth->fetchrow_array;
        $sth->finish;
    }

    my ( $null, $managerid ) = split /--/, $form->{manager};
    $managerid     *= 1;
    $form->{sales} *= 1;

    $form->{employeenumber} =
      $form->update_defaults( $myconfig, "employeenumber", $dbh )
      if !$form->{employeenumber};

    $query = qq|
        UPDATE employees 
           SET employeenumber = ?,
               name = ?,
               address1 = ?,
               address2 = ?,
               city = ?,
               state = ?,
               zipcode = ?,
               country = ?,
               workphone = ?,
               homephone = ?,
               startdate = ?,
               enddate = ?,
               notes = ?,
               role = ?,
               sales = ?,
               email = ?,
               ssn = ?,
               dob = ?,
               iban = ?,
               bic = ?,
               managerid = ?
         WHERE id = ?|;
    $sth = $dbh->prepare($query);
    $form->{dob}       ||= undef;
    $form->{startdate} ||= undef;
    $form->{enddate}   ||= undef;
    $sth->execute(
        $form->{employeenumber}, $form->{name},      $form->{address1},
        $form->{address2},       $form->{city},      $form->{state},
        $form->{zipcode},        $form->{country},   $form->{workphone},
        $form->{homephone},      $form->{startdate}, $form->{enddate},
        $form->{notes},          $form->{role},      $form->{sales},
        $form->{email},          $form->{ssn},       $form->{dob},
        $form->{iban},           $form->{bic},       $managerid,
        $form->{id}
    ) || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

    $dbh->commit;

}

sub delete_employee {
    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    # delete employee

    my $query = qq|
        DELETE FROM employees 
              WHERE id = | . $dbh->quote( $form->{id} );
    $dbh->do($query)
      || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

    $dbh->commit;

}

sub employees {
    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $where = "1 = 1";
    $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
    my @a         = qw(name);
    my $sortorder = $form->sort_order( \@a );

    my $var;

    if ( $form->{startdatefrom} ) {
        $where .=
          " AND e.startdate >= " . $dbh->quote( $form->{startdatefrom} );
    }
    if ( $form->{startdateto} ) {
        $where .= " AND e.startddate <= " . $dbh->quote( $form->{startdateto} );
    }
    if ( $form->{name} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{name} ) );
        $where .= " AND lower(e.name) LIKE $var";
    }
    if ( $form->{notes} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{notes} ) );
        $where .= " AND lower(e.notes) LIKE $var";
    }
    if ( $form->{sales} eq 'Y' ) {
        $where .= " AND e.sales = '1'";
    }
    if ( $form->{status} eq 'orphaned' ) {
        $where .= qq| AND e.login IS NULL|;
    }
    if ( $form->{status} eq 'active' ) {
        $where .= qq| AND e.enddate IS NULL|;
    }
    if ( $form->{status} eq 'inactive' ) {
        $where .= qq| AND e.enddate <= current_date|;
    }

    my $query = qq|
           SELECT e.*, m.name AS manager
             FROM employees e
        LEFT JOIN employees m ON (m.id = e.managerid)
            WHERE $where
         ORDER BY $sortorder|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $ref->{address} = "";
        for (qw(address1 address2 city state zipcode country)) {
            $ref->{address} .= "$ref->{$_} ";
        }
        push @{ $form->{all_employee} }, $ref;
    }

    $sth->finish;
    $dbh->commit;

}

1;