#!/usr/bin/env perl

=for stopwords rfc485 crons login

=head1 NAME

rfc485-user_permissions.pl - Email user permission queries to Lynda

=head1 USAGE

script/crons/quarterly/rfc485-user_permissions.pl

=head1 DESCRIPTION

report all users in location HMDS in order of last login

report all users not in location HMDS with custom permissions

=head1 OPTIONS

=head2 C<-t>

Flag to put program in testing mode (no emails to end users

=head2 C<-d>

Not used

=cut

use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin);    # warn $Bin;
use lib $Bin . '/../../../lib';

use HMDS::Perl;
use version; our $VERSION = qv('1.0.0');

use LIMS::Local::QueryMailer;

# get command line options and setup
use Getopt::Std;
getopts( 'd:t', \my %opt );    # week, testing
my $JUST_TESTING = $opt{t} || 0;    # email to ra.jones only

my @recipients = ( 'raj.secure', 'lynda.blythe.secure' );

# set SQLLibrary to use __DATA__
my $sql_lib = LIMS::Local::QueryLibrary->new( { lib => [<DATA>] } );

# initialise
my $query = LIMS::Local::QueryMailer->new( testing => $JUST_TESTING );

my $sql;

# execute both queries
foreach ( 'HMDS_USERS', 'NONHMDS_USERS' ) {
    $sql = $sql_lib->retr($_);
    $query->run( sql => $sql, sheet_name => $_ );
}

# process results and send email
my $ref_date = DateTime->now->ymd();
$query->make_excel("hilis4_user_audit$ref_date.xlsx");
$query->mail_results(
    recipients => \@recipients,
    subject    => 'HILIS4 Quarterly User Audit'
);

# debugging
if ($JUST_TESTING) {
    $query->save_file('/tmp');
}

__DATA__
[HMDS_USERS]
select
username, first_name, last_name, email, designation, u.active, last_login
from
users u
join user_locations ul on u.user_location_id = ul.id
where
ul.location_name = 'HMDS'
order by last_login
[NONHMDS_USERS]
select
username, first_name, last_name, email, designation,
group_concat(uf.function_name order by uf.function_name) as permissions,
u.active, last_login
from
users u
join user_locations ul on u.user_location_id = ul.id
join user_permission up on up.user_id = u.id
join user_functions uf on up.function_id = uf.id
where
ul.location_name != 'HMDS'
and uf.function_name not in ('print_one','view_authorised','view_history','search')
group by u.id
order by last_login