Editing to be more clear:
I am going to be obtaining 2 queries from the database and storing them as AoH. The 2 queries are:
select
ip_address,
testnet,
t.email as email,
owner,
hr_manager_login as manager
from
lab_view
JOIN CMN_INT.AK_EMPLOYEE on owner = login
JOIN testnets t on t.name = testnet
where
DIVISION like 'TERM%' and TERM_DATE is not null
order by owner
and select login from CMN_INT.AK_EMPLOYEE where DIVISION = q[Terminated Employees]
For my original testing, I wasn't going to dive into the opening/using DBI, so I used dummy data. my $qr is an example of what I would get from the first query and my $qr2 is an example of what I would get from the second query. I want to see if the Manager value in the first AoH is found in the second (terminated employees) AoH. If it IS found, then the manager is a terminated employee and its name shouldn't be pushed to the %manager. I tried testing this out by inserting the below foreach code into my first foreach code, but it didn't work as the Manager Harry was still being pushed and printed.
foreach my $emp2 (@$qr2){
if ($emp->{Manager} ne $emp2->{Login}){
$manager{$emp->{Manager}} = 1; #capture all managers not also terminated related to $name. Done this way for when $name is undef
}
}
I also tried to assign the $emp->{Manager} to a variable before the 2nd foreach and then use if ($M ne $emp2->{Login}), but that also didn't work.
Below is the first part of my testing code with dummy data for the queries I would run, minus all the elsifs :)
my $qr = [
{IP=>'X.Y.Z.51',Testnet=>'bos-por-leg',Owner=>'Edmund', Email => 'bosemail', Manager => 'Tod'},
{IP=>'X.Y.Z.52',Testnet=>'bos-por-2',Owner=>'Edmund', Email => 'boemail2',Manager => 'Tod'},
{IP=>'X.Y.Z.53',Testnet=>'bos-por-leg',Owner=>'Edmund', Email => 'bosemail',Manager => 'Tod'},
{IP=>'X.Y.Z.54',Testnet=>'sqa',Owner=>'Richard', Email => 'sqaemail',Manager => 'Harry'},
{IP=>'X.Y.Z.55',Testnet=>'sqa',Owner=>'Richard', Email => 'sqaemail',Manager => 'Harry'},
{IP=>'X.Y.Z.56',Testnet=>'fll-pro',Owner=>'Larry', Email => 'fllemail',Manager => 'Moe'},
{IP=>'X.Y.Z.57',Testnet=>'fll-pro', Owner=>'', Email => 'fllemail',Manager => 'Tod'},
{IP=>'X.Y.Z.58',Testnet=>'fll-pro2', Owner=>'', Email => 'flemail2', Manager => 'Curly'},
];
my $qr2 = [{Login => 'Tom'},
{Login => 'Dick'},
{Login => 'Harry'},
];
my $len = scalar @$qr;
my $l = $len;
my $a = @$qr[0]->{Owner};
func ($a);
my %ip;
my %test;
my $name;
my %manager;
my $ip_ref;
my $test_ref;
my $man_ref;
sub func{
foreach my $emp (@$qr) {
if ($l > 1 && $emp->{Owner} eq $a) {
$name = $emp->{Owner} || 'Undefined'; #to use with email as $a will change as cycle thru
$ip{$emp->{IP}} = $emp->{Testnet}; #capture all IPs related to owner $name
$test{$emp->{Testnet}} = $emp->{Email}; #capture unique testnets only related to owner $name
foreach my $emp2 (@$qr2){
if ($emp->{Manager} ne $emp2->{Login}){
$manager{$emp->{Manager}} = 1; #capture all managers not also term related to $name. Done this way for when $name is undef
}
}
$l--; #to cycle thru array until reach last row
}
}
}
sub mail_func{
my $n = shift; #user
my $i = shift; #ips
my $t = shift; #testnets
my $m = shift; #managers (multiple if owner is undef) --> to field
print "User name is: $n\n";
my @to_list;
foreach my $value (values %{$t}){
if ($value ne 'bosemail'){
if (grep {$value} @to_list){next;}
else {push(@to_list,$value . '@email.com');}
}
}
foreach my $key (keys %{$m}){push(@to_list,$key . '@email.com');}
print "@to_list\n";
my @body;
while ( my ( $key, $value ) = each %{$i} ) {
my $b = "IP " . $key . " : Testnet " . $value . "\n";
push (@body, $b);
}
print "@body\n";
}
In testing, I get:
User name is: Richard
[email protected] [email protected] ##Harry shouldn't be added
IP X.Y.Z.54 : Testnet sqa
IP X.Y.Z.55 : Testnet sqa
I appreciate all input on how to correct. If you need more code, please let me know. Also to note, the terminated employees list is pretty long, so I'm guessing I probably shouldn't even be comparing each value in one AoH to each value in another AoH via the loop method, but that is all I know :) I am trying to also see if there is a way to do what I want using only 1 query. Thank you.
Unless I have missed something obvious, I think you're making things too complicated.
The following works for me.
WARNING: It uses the 'smartmatch operator' to search for an element within an array. AFAIK this feature is still deemed 'experimental'. Others might be able to comment on the status of that feature - or suggest a more suitable alternative.
use strict;
use warnings;
use feature 'say';
my $qr = [
{IP=>'X.Y.Z.51',Testnet=>'bos-por-leg',Owner=>'Edmund', Email => 'bosemail', Manager => 'Tod'},
{IP=>'X.Y.Z.52',Testnet=>'bos-por-2',Owner=>'Edmund', Email => 'boemail2',Manager => 'Tod'},
{IP=>'X.Y.Z.53',Testnet=>'bos-por-leg',Owner=>'Edmund', Email => 'bosemail',Manager => 'Tod'},
{IP=>'X.Y.Z.54',Testnet=>'sqa',Owner=>'Richard', Email => 'sqaemail',Manager => 'Harry'},
{IP=>'X.Y.Z.55',Testnet=>'sqa',Owner=>'Richard', Email => 'sqaemail',Manager => 'Harry'},
{IP=>'X.Y.Z.56',Testnet=>'fll-pro',Owner=>'Larry', Email => 'fllemail',Manager => 'Moe'},
{IP=>'X.Y.Z.57',Testnet=>'fll-pro', Owner=>'', Email => 'fllemail',Manager => 'Tod'},
{IP=>'X.Y.Z.58',Testnet=>'fll-pro2', Owner=>'', Email => 'flemail2', Manager => 'Curly'},
];
my $qr2 = [{Login => 'Tom'},
{Login => 'Dick'},
{Login => 'Harry'},
];
my @dismissed = map { $_->{Login} } @$qr2;
my @eligible = grep { !($_->{Manager} ~~ @dismissed) } @$qr;
say $_->{Testnet}, ', ', $_->{Email} foreach @eligible;
Alternative DB queries
Since you tagged the question with Perl rather than DBI, this is parenthetical - but I think is worth noting nonetheless.
You asked:
I am trying to also see if there is a way to do what I want using only 1 query
I think there is - using the NOT IN operator. Something similar to the following will probably work - although you might need to alter the syntax slightly depending on the database server you are using.
Simple add another condition to your WHERE clause, so
where
DIVISION like 'TERM%' and TERM_DATE is not null
would become
where
DIVISION like 'TERM%' and TERM_DATE is not null
AND
hr_manager_login NOT IN (select login from CMN_INT.AK_EMPLOYEE where DIVISION = q[Terminated Employees])
If the number of terminated employees is extremely long (you intimate it is 'quite long' - but don't specify exactly how long) there may be performance implications for using NOT IN, but I think that is probably not the case here.