#!/s/std/bin/perl

# $Id: pmplist.pl,v 1.2 2023/09/20 21:08:31 dparter Exp $

# pmplist.pl dumps out email addresses of PMP students for mailing lists
# and the "alternates" email list
#

use strict;
use warnings;
use Getopt::Std;
use DBI;
use CSL::Pg;
use CSL::DBs;

our($opt_o, $opt_s, $opt_d);

my $schema = 'csdb';
my $debug = 0;

getopts('do:s:');

if ($opt_o) {
    open(LISTFILE, ">$opt_o") || die "can't open $opt_o: $!";
    open(ALTFILE, ">$opt_o" . "-alts") || die "can't open $opt_o" ."-alts: $!";
} else {
    die "need -o out_name (will create outname and outname-alts)";
}
if ($opt_s) {
    $schema = $opt_s;
}
if ($opt_d) {
    $debug = 1;
}

my $dbh = DBI->connect($CSL::DBs::csdb)
  or die "Could not connect to CSDB: $!";

my $query_body = "$schema.people p where (p.status like '%M%') and (p.login is not null or (p.emailaddr is not NULL and p.emailaddr like '%@%.%' and p.emailaddr not like '%@%.cs.wisc.edu' and p.emailaddr not like '%@%.stat.wisc.edu' )) and p.onleave = 0";

# Header
# no header this time

# get the data now
my $grad_query = $dbh->prepare(
    "select p.login, p.emailaddr from "
    . $query_body 
    . " order by lower(p.lastname), lower(p.firstname), p.login");
my $alt_query =  $dbh->prepare(
    "select e.o365_address from $schema.o365_email_addresses e, " 
    . $query_body 
    . " and p.login = e.domain_address and e.domain = 'cs.wisc.edu'");

$grad_query->execute;
while (my $row = $grad_query->fetchrow_hashref) {
    my $email = ($row->{login} eq "") ? $row->{emailaddr} : $row->{login} . '@cs.wisc.edu';
  print LISTFILE "$email\n";
}
$grad_query->finish;
close(LISTFILE);

$alt_query->execute;
while (my $row = $alt_query->fetchrow_arrayref) {
    print ALTFILE "$$row[0]\n";
}
$alt_query->finish;
close(ALTFILE);

# Footer
# no footer this time

# clean up
$dbh->disconnect;
