Im just having trouble with updating that table now :(
When admin selects a user profile and uses the multiselect to assign them to departments the script pushes each department id selected into an array. I also have all department id's the user is currently in, in another array.
So I need to delete rows from the staff allocation table that currently exist and that havent been selected in the form (ie if a department is removed) and also insert rows that dont already exist in the database (eg if admin adds a department to a staff member)
This is turning out to be tricky. I've managed to get it to do additions but it doesnt want to delete if I deselect a couple of departments and hit update.
Ugh.
Here's what Im using:
# Do any necessary updates to allocations.
my $sth = $DB->select( ['Department'], 'Allocation', { UserID => $FORM->{userid} } );
while (my $row = $sth->fetchrow_hashref) {
push @all, $row->{Department};
}
if ($#all > -1) {
for (@all) {
if (grep { !/^\Q$_\E$/ } @{$FORM->{Departments}}) {
$DB->delete( 'Allocation', { Department => $_ } );
}
elsif (grep { /^\Q$_\E$/ } @{$FORM->{Departments}}) {
next;
}
else {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}
else {
for (@{$FORM->{Departments}}) {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}
It is being a major ass.
When admin selects a user profile and uses the multiselect to assign them to departments the script pushes each department id selected into an array. I also have all department id's the user is currently in, in another array.
So I need to delete rows from the staff allocation table that currently exist and that havent been selected in the form (ie if a department is removed) and also insert rows that dont already exist in the database (eg if admin adds a department to a staff member)
This is turning out to be tricky. I've managed to get it to do additions but it doesnt want to delete if I deselect a couple of departments and hit update.
Ugh.
Here's what Im using:
Code:
if (ref $FORM->{Departments} eq 'ARRAY') { # Do any necessary updates to allocations.
my $sth = $DB->select( ['Department'], 'Allocation', { UserID => $FORM->{userid} } );
while (my $row = $sth->fetchrow_hashref) {
push @all, $row->{Department};
}
if ($#all > -1) {
for (@all) {
if (grep { !/^\Q$_\E$/ } @{$FORM->{Departments}}) {
$DB->delete( 'Allocation', { Department => $_ } );
}
elsif (grep { /^\Q$_\E$/ } @{$FORM->{Departments}}) {
next;
}
else {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}
else {
for (@{$FORM->{Departments}}) {
$DB->insert( 'Allocation', [qw(UserID Department)], [$FORM->{userid},$_] );
}
}
}
It is being a major ass.