#!/usr/bin/perl -Ilibs
use strict;
use warnings;

package API::Employee;

our $Config;
BEGIN{
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{Website_libs});

use ECS::SQL;
use ECS::Site;

my $dbh = ECS::SQL::connect();
my $dbhr = ECS::SQL::connect('SLAVE');

our %VIEWS = (
    'Product Admin' => 1,
    'Order Admin'   => 1,
    'Ticketing'     => 1,
    );
    
our $customer_info_query = "SELECT
	CONCAT('employeeNumber = ', COALESCE( e.employeeNumber, '' )) AS employeeNumber,
	CONCAT('nameFirst = ', COALESCE( e.nameFirst, '' )) AS nameFirst,
	CONCAT('nameLast = ', COALESCE( e.nameLast, '' )) AS nameLast,
	CONCAT('username = ', COALESCE( e.username, '' )) AS username,
	CONCAT('email = ', COALESCE( e.email, '' )) AS email,
	CONCAT('manager = ', COALESCE( manager.username, '' )) AS manager,
	CONCAT('department = ', COALESCE( e.department,  '' )) AS department,
	CONCAT('active = ', COALESCE( e.active, 'Y' )) AS active
	FROM employeeInfo e LEFT JOIN employeeInfo manager ON e.managerID = manager.employeeID 
	WHERE e.employeeID = ?";
	
# Add new employee, warehouse_employee, or group
##############################################################
sub add{

	my $ref = shift;
	my $vars = shift;
	my $reply = {
		'success'	=> 1,
		'update_DB'	=> $Config->{update_DB},
		'error'		=> undef,
	};
	

	# IF we want to update DB (Determined by API::Config)
	# --------------------------------------------------
	if ($Config->{update_DB}){
		if ( defined($vars->{saveEmployee}) ){
			if( defined($vars->{action}) && ($vars->{action} eq "addWarehouseEmployee")){
				$reply->{empID} = &add_warehouse_employee($vars);
				if(!$reply->{empID}){
					$reply->{success} = 0;
					$reply->{error} = "Unable to add warehouse employee.";
				}
				return $reply;
			}else{
				$reply->{empID} = &add_employee({
			        'dbh'   => $dbh,
			        'info'  => $vars,
			    });
			    	
			    if(!$reply->{empID}){
			    	$reply->{success} = 0;
			    	$reply->{error} = "Unable to add employee";
			    }

				# Add was successful, so pass the employeeID around for use
				$vars->{employeeID} = $reply->{empID};

				# Apply the accordion permissions
				$reply->{success} = &set_employee_permissions({
			        'dbh'   => $dbh,
			        'info'  => $vars,
			    });

			    if(!$reply->{success}){
			    	$reply->{success} = 0;
			    	$reply->{error} = "Unable to add permissions to employee";
			    }

				$reply->{success} = &add_employee_group({
			        'dbh'   => $dbh,
			        'info'  => $vars,
			    });
			    	
			    if(!$reply->{success}){
			    	$reply->{success} = 0;
			    	$reply->{error} = "Unable to add groups to employee";
			    }
			    log_changes({
			       employeeID  => $vars->{employeeID},
				   info        => $vars,   
			    });
                return $reply;
            }
		}elsif (defined($vars->{saveGroup})){
			$reply->{groupID} = &add_group({
	        	'dbh'   => $dbh,
	        	'info'  => $vars,
	        });

	        if(!$reply->{groupID}){
	        	$reply->{success} = 0;
	        	$reply->{error} = "Unable to add group.";
	        }
	        return $reply;
		}else{
			die "Method add in class Employee called without save information";
		}
	}
	return $reply;
}

# Update employee, warehouse_employee, or group
##############################################################
sub update{

	my $ref = shift;
	my $vars = shift;
	my $reply = {
		'success'	=> 1,
		'update_DB'	=> $Config->{update_DB},
		'error'		=> undef,
	};
	if ($Config->{update_DB}){
		if ( defined( $vars->{saveEmployee} || $vars->{saveGroups} ) ){
			if (defined ( $vars->{editWarehouseEmployee}) ){
				my $rc = &save_warehouse_employee($vars);
					if(!$rc){
						$reply->{success} = 0;
						$reply->{error} = "Unable to update warehouse employee.";
					}
					$reply->{result} = $rc;
			}else{
			    my $rc = &update_employee({
			        'dbh'   => $dbh,
			        'info'  => $vars,
			        });
		    	if(!$rc){
					$reply->{success} = 0;
					$reply->{error} = "Unable to update employee.";
				}
				$reply->{result} = $rc;

				# Permissions workflow:
				# 1) Apply/revoke permissions based on anything that was manually selected
				# 2) Update employee's groups
				# 3) Apply/revoke permissions based on groups
				# Due to how the accordion works we can't apply those permissions last, or
				# they overwrite any group changes.
				my $result;
				if(defined $vars->{saveEmployee}) {
					$result = &set_employee_permissions({
				        'dbh'   => $dbh,
				        'info'  => $vars,
				    });
				    if(!$result){
				    	$reply->{success} = 0;
				    	$reply->{error} = "Unable to set employee permissions."; 
				    }
				}

				if(defined $vars->{saveGroups}) {
					$result = &update_employee_groups({
						'dbh'  => $dbh,
						'info' => $vars,
					});
					if(!$result) {
						$reply->{success} = 0;
						$reply->{error} = "Unable to update employee groups.";
					}
				}
			    
			    my %views = ();
			    for my $app (keys %VIEWS) {
			        $views{$app} = $vars->{"default_view_$app"};
			    }
			    my $editEmployeeID = $vars->{editEmployee};
			    $result = &set_views({
			        'dbh'           => $dbh,
			        'employee_id'   => $editEmployeeID,
			        'views'         => \%views,
			        'info'         => $vars,
			        });

			    if(!$result){
			    	$reply->{success} = 0;
			    	$reply->{error} = "Unable to set employee views."; 
			    }
			    log_changes({
					'employeeID'   => $editEmployeeID,
					'info' 			=> $vars,
			    }); 
			    $reply->{result} = $result;
			        
				return $reply;
			}
		}elsif (defined ( $vars->{saveGroup}) ){
			
			my @permissions = ();
		    for my $key (keys %$vars) {
		        if($vars->{$key}
		            and $key =~ m/^perm_(\d+)$/
		            ) {
		            my $permission = $1;
		            push(@permissions, $permission);
		            delete $vars->{$key};
		        }
		    }
		
		    my $result = &update_group({
		        'dbh'   => $dbh,
		        'info'  => $vars,
		    });
		    if(!$result){
		    	$reply->{success} = 0;
		    	$reply->{error} = "Unable to update group";
		    }

			# If we got a list of employees, loop over them and update their permissions
			if(defined $vars->{'employees'}) {
				$result = undef;
				my @employees = @{$vars->{'employees'}};
				foreach my $employee(@employees) {
					$vars->{'employeeID'} = $employee;
					$result = &set_employee_permissions({
						'dbh'  => $dbh,
						'info' => $vars,
						});
					last if not $result;
				}
			}
			if(!$result) {
				$reply->{success} = 0;
				$reply->{error} = "Unable to set employee permissions.";
			}
		}else{
			$reply->{success} = 0;
			$reply->{error} = "Unable to return from saving group update.";
		}
	}
	return $reply;
}

# Functions from EmployeeAdmin.pm that write to DB
###############################################################
sub add_warehouse_employee($) {
    my $form_data = shift;

    if($form_data->{'userID'} !~ m/^\d+$/) {
        return;
    }
    if($form_data->{'name'} !~ m/^[\w ]{3,}$/) {
        return;
    }
    if($form_data->{'override'} !~ m/^(|General|Manager)$/) {
        return;
    }

    my $manager = "N";
    if($form_data->{'override'}) {
        $manager = "Y";
    }

    my $superManager = "N";
    if($form_data->{'override'} eq "Manager") {
        $superManager = "Y";
    }

    my $query = "insert
        into
            scanner_user
        set
            userID = ?,
            name = ?,
            manager = ?,
            superManager = ?,
            active = 'Y'";
    my $add_employee = $dbh->prepare($query);
    $add_employee->execute(
        $form_data->{'userID'},
        $form_data->{'name'},
        $manager,
        $superManager,
        )
        or return;
    my $insertid = $dbh->{'mysql_insertid'};

    $query = "insert
        into
            scanner_role
        set
            suID = ?,
            role = ?";
    my $add_role = $dbh->prepare($query);

    if($form_data->{'canPickPack'} eq "Y") {
        $add_role->execute(
            $insertid,
            "PickPack",
            );
    }
    if($form_data->{'canReceive'} eq "Y") {
        $add_role->execute(
            $insertid,
            "Receive",
            );
    }

    return $insertid;
}
###############################################################
sub save_warehouse_employee($) {
    my $form_data = shift;
    if($form_data->{'editWarehouseEmployee'} !~ m/^\d+$/) {
        return;
    }
    if($form_data->{'userID'} !~ m/^\d+$/) {
        return;
    }
    if($form_data->{'name'} !~ m/^[\w ]{3,}$/) {
        return;
    }
    if($form_data->{'override'} !~ m/^(|General|Manager)$/) {
        return;
    }

    if($form_data->{'active'} ne "N"
        and $form_data->{'active'} ne "Y"
        ) {
        return;
    }

    my $manager = "N";
    if($form_data->{'override'}) {
        $manager = "Y";
    }

    my $superManager = "N";
    if($form_data->{'override'} eq "Manager") {
        $superManager = "Y";
    }

    my $query = "update
            scanner_user
        set
            scanner_user.userID = ?,
            scanner_user.name = ?,
            scanner_user.active = ?,
            scanner_user.manager = ?,
            scanner_user.superManager = ?
        where
            scanner_user.id = ?";
    my $update_user = $dbh->prepare($query);

    $update_user->execute(
        $form_data->{'userID'},
        $form_data->{'name'},
        $form_data->{'active'},
        $manager,
        $superManager,
        $form_data->{'editWarehouseEmployee'},
        );

    $dbh->do("delete from scanner_role where suID = " .
        $form_data->{'editWarehouseEmployee'});

    $query = "insert
        into
            scanner_role
        set
            suID = ?,
            role = ?";
    my $add_role = $dbh->prepare($query);

    if($form_data->{'canPickPack'} eq "Y") {
        $add_role->execute(
            $form_data->{'editWarehouseEmployee'},
            "PickPack",
            );
    }
    if($form_data->{'canReceive'} eq "Y") {
        $add_role->execute(
            $form_data->{'editWarehouseEmployee'},
            "Receive",
            );
    }
    return 1;
}
###############################################################
sub update_employee(%){
	
    my $p = shift;
    my $dbh = $p->{'dbh'};
    my $dbhr = $p->{'dbhr'};
    
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
    }
    if(not defined $dbhr) {
        $dbhr = ECS::SQL::connect('SLAVE');
    }

    my $info = $p->{'info'} || {};
	my $unblockUserSql = '';
	
	if($info->{'unblockUser'})
	{
		$unblockUserSql = 'employeeInfo.invalidAttempts = 0, employeeInfo.lastInvalidAttempt = NULL,';
	}
	
    my $query = "update
            employeeInfo
        set
            employeeInfo.employeeNumber = ?,
            employeeInfo.nameFirst = ?,
            employeeInfo.nameLast = ?,
            employeeInfo.username = ?,
            employeeInfo.email = ?,
            employeeInfo.managerID = ?,
            employeeInfo.department = ?,
            $unblockUserSql
            employeeInfo.active = ?
        where
            employeeInfo.employeeID = ?
        limit 1";
    my $update_employee = $dbh->prepare($query);
    my $log_employee = $dbhr->prepare( $customer_info_query );
    $log_employee->execute( $info->{employeeID} );
    my $old_info =  { map { $_ => -1 } values %{ $log_employee->fetchrow_hashref() || {} } };
	

    $update_employee->execute(
        $info->{'employeeNumber'} || undef,
        $info->{'nameFirst'} || undef,
        $info->{'nameLast'} || undef,
        $info->{'username'} || undef,
        $info->{'email'} || undef,
        $info->{'managerID'} || undef,
        $info->{'department'} || undef,
        $info->{'active'} || "Y",
        $info->{'employeeID'},
        );

    $log_employee->execute( $info->{employeeID} );
    $old_info->{$_}++ for values %{ $log_employee->fetchrow_hashref() || {} };
    $p->{info}{changes}{ fields } = $old_info; 

    return 1;
	
}
###############################################################
sub update_group(%) {
    my $p = shift;

    my $dbh = $p->{'dbh'};
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
    }

    my $info = $p->{'info'} || {};

	my $permissions = $info->{'permissions'}->[0];
	my $query = "select
		permissionGroup.name
	from
		permissionGroup
	where
		permissionGroup.groupID = ?";
	my $group_name = $dbh->prepare($query);
	$group_name->execute(
		$info->{'groupID'},
	) or return 0;

	# Check if the group name was changed
	my $name = $group_name->fetchrow_hashref();
	if($name->{'name'} ne $info->{'name'}) {
		$query = "update
			permissionGroup
		set
			permissionGroup.name = ?
		where
			permissionGroup.groupID = ?";
		my $update_group = $dbh->prepare($query);
		$update_group->execute(
			$info->{'name'},
			$info->{'groupID'},
		) or return 0;
	}

	# Update permissions that need to be updated
	$query = "select
		groupPermission.permissionID
	from
		groupPermission
	where
		groupPermission.groupID = ?";
	my $group_permissions = $dbh->prepare($query);
	$group_permissions->execute(
		$info->{'groupID'},
	) or return 0;

	my @ids;
	while(my $permission_id = $group_permissions->fetchrow_arrayref()) {
		push @ids, @$permission_id;
	}

	$query = "insert
	into
		groupPermission (groupID, permissionID)
	values
		(?, ?)";
	my $update = $dbh->prepare($query);
	$query = "delete
	from
		groupPermission
	where
		groupPermission.groupID = ?
	and
		groupPermission.permissionID = ?";
	my $remove = $dbh->prepare($query);

	foreach my $key(keys %$permissions) {
		if($permissions->{$key} == 1) {
			$key =~ /^perm_(\d+)/;
			my $t = $1;
			if(!(grep(/^$t$/, @ids))) {
				$update->execute(
					$info->{'groupID'},
					$t,
				) or return 0;
			}
		}
		else {
			$key =~ /^perm_(\d+)/;
			my $t = $1;
			$remove->execute(
				$info->{'groupID'},
				$t,
			) or return 0;
		}
	}
    return 1;
}
###############################################################
sub set_employee_permissions(%) {
    my $p = shift;
   
    my $dbh = $p->{'dbh'};
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
	}
	my $info = $p->{'info'} || {};

	my $permissions = $info->{'permissions'}->[0];

	my $allSites = ECS::Site::getAll;
	my @sitePermissions;
	foreach my $site (@$allSites) {
		if (exists $info->{'Permission' . $site->name}) {
			push @sitePermissions, $site->siteID;
		}
	}

	my $query = "select
			employeePermission.permissionID,
			employeePermission.siteID
		from
			employeePermission
		where
			employeePermission.employeeID = ?";
	my $emp_permissions = $dbh->prepare($query);
	$emp_permissions->execute(
		$info->{'employeeID'},
		)
		or return 0;

	my @ids;
	while(my $permission_id = $emp_permissions->fetchrow_arrayref()) {
		push @ids, $permission_id->[0] . '_' . $permission_id->[1];
	}

	$query = "insert
	into
		employeePermission (employeeID, permissionID, siteID)
	values
		(?, ?, ?)";
	my $update = $dbh->prepare($query);

	$query = "delete
	from
		employeePermission
	where
		employeePermission.employeeID = ?
	and
		employeePermission.permissionID = ?
	and
		employeePermission.siteID = ?";

	my $remove = $dbh->prepare($query);
	$query = "SELECT CONCAT(permission.category,' => ',permission.name,'[',site.name,']') AS permission
				FROM employeeInfo
				LEFT JOIN employeePermission USING (employeeID)
				LEFT JOIN employeeGroup USING (employeeID)
				LEFT JOIN permission USING (permissionID)
				LEFT JOIN groupPermission USING (groupID, permissionID)
				JOIN site ON employeePermission.siteID = site.siteID
				WHERE permissionID IS NOT NULL AND  groupID IS NULL AND employeeID = ?";

	my $log = $dbh->prepare( $query );
	$log->execute( $info->{employeeID} );
	my $old_perms = $log->fetchall_hashref( 'permission' ) || {};
	$old_perms->{$_} = -1 for keys %$old_perms;

	foreach my $key(keys %$permissions) {
		if($permissions->{$key} == 1) {
			$key =~ /^perm_(\d+)/;
			my $t = $1;
			my @selectedPerms;
			foreach my $site (@sitePermissions) {
				push @selectedPerms, $t . '_' . $site;
			}
			foreach my $site (@$allSites) {
				my $thisPermission = $t . '_' . $site->siteID;
				if(grep(/^$thisPermission$/, @selectedPerms) && !(scalar grep(/^$thisPermission$/, @ids))) {
					$update->execute(
						$info->{'employeeID'},
						$t,
						$site->siteID,
					) or return 0;
				}
				if (grep(/^$thisPermission$/, @ids) && !(grep(/^$thisPermission$/, @selectedPerms))) {
					$remove->execute(
						$info->{'employeeID'},
						$t,
						$site->siteID,
					) or return 0;
				}
			}
		}
		else {
			$key =~ /^perm_(\d+)/;
			my $t = $1;
			foreach my $site (@$allSites) {
				my $thisPermission = $t . '_' . $site->siteID;
				if(grep(/^$thisPermission$/, @ids)) {
					# If group change then do nothing
					if(not defined $info->{'saveGroup'}) {
						$remove->execute(
							$info->{'employeeID'},
							$t,
							$site->siteID,
						) or return 0;
					}
				}
			}
		}
	}
    $log->execute( $info->{employeeID} );
    my $new_perms = $log->fetchall_hashref( 'permission' )||{};
    $old_perms->{$_}++ for keys %$new_perms;
    $p->{info}{changes}{ permissions } = $old_perms;

    return 1;

}
###############################################################
sub add_employee(%) {
    my $p = shift;
    my $dbh = $p->{'dbh'};
    my $dbhr = $p->{'dbhr'};
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
    }
    if(not defined $dbhr) {
        $dbhr = ECS::SQL::connect('SLAVE');
    }

    my $info = $p->{'info'} || {};

    my $query = "insert into employeeInfo
        set
            employeeNumber = ?,
            nameFirst = ?,
            nameLast = ?,
            username = ?,
            email = ?,
            managerID = ?,
            department = ?";
    my $add_employee = $dbh->prepare($query);

    my $success = $add_employee->execute(
        $info->{'employeeNumber'} || undef,
        $info->{'nameFirst'} || undef,
        $info->{'nameLast'} || undef,
        $info->{'username'} || undef,
        $info->{'email'} || undef,
        $info->{'managerID'} || undef,
        $info->{'department'} || undef,
        );
        if(!$success){
        	warn "Unable to execute query.";
        	return 0;
        };

    my $employee_id = $dbh->{'mysql_insertid'};
    my $log_employee = $dbhr->prepare( $customer_info_query );
    $log_employee->execute( $employee_id );
    my $old_info =  { map { $_ => 1 } values %{ $log_employee->fetchrow_hashref() || {} } };
    $p->{info}{changes}{fields} = $old_info;
	

    return $employee_id;
}
###############################################################
sub add_group(%) {
    my $p = shift;

    my $dbh = $p->{'dbh'};
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
    }

    my $info = $p->{'info'} || {};

    my $query = "insert
        into
            permissionGroup
        set
            name = ?";
    my $add_group = $dbh->prepare($query);
    $add_group->execute(
        $info->{'name'},
        )
        or $dbh->do("rollback")
        and return 0;

    my $group = $dbh->{'mysql_insertid'};

    my $worked = 1;
    for my $permission (@{$info->{'permissions'}}) {
        my $res = &add_group_permission({
            'dbh'           => $dbh,
            'group'         => $group,
            'permission'    => $permission,
            });

        if(not $res) {
            $worked = 0;
            last;
        }
    }

    if($worked) {
        return $group;
    }
    else {
        return 0;
    }
}
###############################################################
sub add_group_permission(%) {
    my $p = shift;

    my $dbh = $p->{'dbh'};
    if(not defined $dbh) {
        $dbh = ECS::SQL::connect();
    }

    my $query = "insert
        into
            groupPermission
        set
            groupID = ?,
            permissionID = ?";
    my $add_group_permission = $dbh->prepare($query);
    $add_group_permission->execute(
        $p->{'group'},
        $p->{'permission'},
        )
        and return 1;
    return 0;
}
###############################################################
sub set_views(%) {
    my $p = shift;
	my $changes = {};

    my $query = "insert into
            employeeView
        set
            employeeID = ?,
            applicationName = ?,
            viewName = ?
        on duplicate key update
            viewName = values(viewName)";
    my $set_view = $p->{'dbh'}->prepare($query);
    $query = "SELECT CONCAT(applicationName , ' => ', viewName ) AS view FROM   
                employeeView WHERE employeeID = ?";
    my $get_views = $p->{dbh}->prepare( $query );

    $query = "delete from
            employeeView
        where
            employeeView.employeeID = ?
            and employeeView.applicationName = ?
        limit 1";

	$get_views->execute( $p->{employee_id} );
	my $old_views = $get_views->fetchall_hashref( 'view' )||{};
	$old_views ||= {};
	$_ = -1 for values %$old_views;

    my $delete_view = $p->{'dbh'}->prepare($query);

    for my $app (keys %{$p->{'views'}}) {
        my $view = $p->{'views'}->{$app};
        if($view) {
            $set_view->execute(
                $p->{'employee_id'},
                $app,
                $view,
                );
        }
        else {
            $delete_view->execute(
                $p->{'employee_id'},
                $app,
                );
        }
    }

    $get_views->execute( $p->{employee_id} );
    my $new_views = $get_views->fetchall_hashref( 'view' )||{};
    $new_views ||= {};
    $old_views->{ $_ }++ for keys %$new_views;
    $p->{info}{changes}{views} = $old_views if $old_views && %$old_views;
	

    return 1;
}

##############################################################
sub update_employee_groups(%) {

	my $p = shift;

	my $dbh = $p->{'dbh'};
	if(not defined $dbh) {
		$dbh = ECS::SQL::connect();
	}

	my $info = $p->{'info'} || {};
	
	my $allSites = ECS::Site::getAll;
	my @sitePermissions;
	foreach my $site (@$allSites) {
		if (exists $info->{'Permission' . $site->name}) {
			push @sitePermissions, $site->siteID;
		}
	}

	my $groups = $info->{'groups'}->[0];
	
	my $query = "select
		employeeGroup.groupID,
		permissionGroup.name
	from
		employeeGroup
		JOIN 
		permissionGroup 
		USING (groupID)
	where
		employeeGroup.employeeID = ?";
	my $employee_groups = $dbh->prepare($query);
	my $old_groups = {};
	$employee_groups->execute(
		$info->{'employeeID'},
	) or return 0;


	my @ids;
	while(my $group = $employee_groups->fetchrow_hashref()) {
		push @ids, $group->{'groupID'};
		$old_groups->{ $group->{name} } = -1;
	}

	# Stuck a lot of query prepares out here to keep the DB load down.
	$query = "insert
	into
		employeeGroup (groupID, employeeID)
	values
		(?, ?)";
	my $update = $dbh->prepare($query);
	$query = "select
		groupPermission.permissionID
	from
		groupPermission
	where
		groupPermission.groupID = ?";
	my $group_perms = $dbh->prepare($query);
	$query = "select
		employeePermission.permissionID,
		employeePermission.siteID
	from
		employeePermission
	where
		employeePermission.employeeID = ?";
	my $emp_perms_sel = $dbh->prepare($query);
	$query = "insert
	into
		employeePermission (employeeID, permissionID, siteID)
	values
		(?, ?, ?)";
	my $emp_perms_ins = $dbh->prepare($query);
	$query = "delete
	from
		employeeGroup
	where
		employeeGroup.groupID = ?
	and
		employeeGroup.employeeID = ?";
	my $eg_remove = $dbh->prepare($query);
	$query = "delete
	from
		employeePermission
	where
		employeePermission.employeeID = ?
	and
		employeePermission.permissionID = ?
	and
		employeePermission.siteID = ?";
		
	my $ep_remove = $dbh->prepare($query);

	# For each group:
	# - If adding to a group, add the permissions for that group to the employee
	# - If removing from a group, remove the permissions for that group from the employee
	foreach my $key(keys %$groups) {
		if($groups->{$key} == 1) {
			$key =~ /^group_(\d+)/;
			my $t = $1;
			if(!(grep(/^$t$/, @ids))) {
				$update->execute(
					$t,
					$info->{'employeeID'},
				) or return 0;
			}

			$group_perms->execute(
				$t,
			) or return 0;

			my @group_perm_list;
			while(my $perm = $group_perms->fetchrow_arrayref()) {
				push @group_perm_list, @$perm;
			}

			$emp_perms_sel->execute(
				$info->{'employeeID'},
			) or return 0;

			my @emp_perm_list;
			while(my $perm = $emp_perms_sel->fetchrow_arrayref()) {
				push @emp_perm_list, ($perm->[0]."_".$perm->[1]);
			}
			
			foreach my $perm(@group_perm_list) {
				my @selectedPerms;
				foreach my $site (@sitePermissions) {
					push @selectedPerms, $perm . '_' . $site;
				}
				use Data::Dumper;
				foreach my $site (@$allSites) {
					my $thisPermission = $perm . '_' . $site->siteID;
					if((scalar grep(/^$thisPermission$/, @selectedPerms)) && !(scalar grep(/^$thisPermission$/, @emp_perm_list))) {
						$emp_perms_ins->execute(
								$info->{'employeeID'},
								$perm,
								$site->siteID
							) or return 0;
						
					}
					if ((scalar grep(/^$thisPermission$/, @emp_perm_list)) && !(scalar grep(/^$thisPermission$/, @selectedPerms))) {
						$ep_remove->execute(
							$info->{'employeeID'},
							$perm,
							$site->siteID,
							) or return 0;
					}
				}
				
			}
		}
		else {
			$key =~ /^group_(\d+)/;
			my $t = $1;
			if(grep(/^$t$/, @ids)) {
				$eg_remove->execute(
					$t,
					$info->{'employeeID'},
				) or return 0;

				$group_perms->execute(
					$t,
				) or return 0;

				my @perm_list;
				while(my $perm = $group_perms->fetchrow_arrayref()) {
					push @perm_list, @$perm;
				}
				
				foreach my $site(@sitePermissions){
					foreach my $perm(@perm_list) {
						$ep_remove->execute(
							$info->{'employeeID'},
							$perm,
							$site,
						) or return 0;
					}
				}
			}
		}
	}
	$employee_groups->execute( $info->{'employeeID'} ); 
	my $new_groups = $employee_groups->fetchall_hashref( 'name' )||{};
	$old_groups->{ $_ }++ for keys %$new_groups;
	$p->{info}{changes}{groups} = $old_groups;
	return 1;
}

##############################################################
sub add_employee_group(%) {
	my $p = shift;

	my $dbh = $p->{'dbh'};
	if(not defined $dbh) {
		$dbh = ECS::SQL::connect();
	}

	my $info = $p->{'info'} || {};

	my $groups = $info->{'groups'}->[0];

	my $query = "insert
	into
		employeeGroup (employeeID, groupID)
	values
		(?, ?)";
	my $employee_group = $dbh->prepare($query);
	$query = "select
		groupPermission.permissionID
	from
		groupPermission
	where
		groupID = ?";
	my $group_perms = $dbh->prepare($query);
	$query = "insert
	into
		employeePermission (employeeID, permissionID)
	values
		(?, ?)";
	my $employee_permission = $dbh->prepare($query);

	$query = "select
		employeeGroup.groupID,
		permissionGroup.name
	from
		employeeGroup
		JOIN 
		permissionGroup 
		USING (groupID)
	where
		employeeGroup.employeeID = ?";
	my $employee_groups = $dbh->prepare($query);
	my $old_groups = {};
	$employee_groups->execute(
		$info->{'employeeID'},
	) or return 0;
	$employee_groups->execute( $info->{employeeID} );
	$old_groups = $employee_groups->fetchall_hashref( 'name' )||{};
	$old_groups->{$_ } = -1 for keys %$old_groups;

	foreach my $key(keys %$groups) {
		if($groups->{$key} == 1) {
			$key =~ /^group_(\d+)/;
			my $t = $1;
			$employee_group->execute(
				$info->{'employeeID'},
				$t,
			) or return 0;


			$group_perms->execute(
				$t,
			) or return 0;

			# Add permissions to the employee
			while(my $perm = $group_perms->fetchrow_hashref()) {
				$employee_permission->execute(
					$info->{'employeeID'},
					$perm->{'permissionID'},
				) or return 0;
			}
		}
	}
	$employee_groups->execute( $info->{employeeID} );
	my $new_groups = $employee_groups->fetchall_hashref( 'name' )||{};
	$old_groups->{$_ }++ for keys %$new_groups;
	$p->{info}{changes}{groups} = $old_groups;

	return 1;
}

# not an aPI method
sub log_changes {
	my $p = shift;
	my ($employeeID, $info) = @$p{ qw/employeeID info/ };
	my $record = {};
	$info ||= {};
	my $changes = $info->{changes} || {};
	for my $change (keys %$changes) {
		$record->{"${change}Modified"} = join "\n" =>
			(grep { m{^[+-]} }
			map { (qw/0 + -/)[$info->{changes}{$change}{$_}] . $_ } 
			sort { 
				$changes->{$change}{ $a } <=> $changes->{$change}{ $b }
				|| $a cmp $b 
			} keys %{$changes->{$change}}), '';
	}
	$record->{employeeID} = $info->{ source_employeeID } || $employeeID;
	$record->{targetEmployeeID} = $employeeID;
	my $log_sql = do {
		local $" = ",";
		"INSERT INTO employeeAdminLog (@{[ keys %$record ]})
		 VALUES (@{[ ('?')x(keys %$record) ]})";
	};
	my $log = $dbh->prepare( $log_sql );
	$log->execute( values %$record );
	1;
}

1;
