#!/usr/bin/env perl

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

use Spreadsheet::ParseExcel::Simple;
use Data::Dumper;

use LIMS::Local::Utils;
use LIMS::Local::ScriptHelpers;

my $tools = LIMS::Local::ScriptHelpers->new();
my $dbix  = $tools->dbix();

my $src = 'Trustwide Leavers.xls';
my $data = parse_xl($src); # warn Dumper $data;

my $sql = q!select designation from users where first_name = ? and last_name = ?
    and active = 'yes'!;

my $trim = sub { LIMS::Local::Utils::trim(@_) };

for my $user(@$data) {
    map { $_ = &$trim($_) } @$user; # warn Dumper $user; # trim all sloppy fields
    my ( $fname, $lname, $role, $location ) = @{$user}[5,6,8,9];

    # if first_name & surname match an existing user, extract users.designation:
    if ( my $designation = $dbix->query( $sql, $fname, $lname )->list ) {
        printf "%s %s :: %s, %s [%s]\n", # print xl data + hilis4.users.designation:
            $fname, uc $lname, $role, $location, $designation;
    }
}

sub parse_xl {
	my $file = shift;

    my $xls = Spreadsheet::ParseExcel::Simple->read($file) or die $!;

    my @worksheets = $xls->sheets;
    my $worksheet  = $worksheets[0];

	my @data = (); # create array of row arrayrefs:
    while ($worksheet->has_data) {
		push @data, [ $worksheet->next_row ]; # next_row() = array mode
    }
    return \@data;
}