#!/s/std/bin/perl

# $Id: k5login.pl,v 1.14 2008/12/16 21:57:33 koczan Exp $

# orpmaillists.pl dumps course mailing list rosters.
# This is now obsolete/unused.

use strict;
use warnings;

use DBI;
use CSL::Pg;
use CSL::DBs;

# we need a directory for this, and it should already be created
my $ORPLISTDIR = shift @ARGV;
if (!defined $ORPLISTDIR || $ORPLISTDIR eq '') {
  print STDERR "ERROR: $ORPLISTDIR undefined.\n";
  print STDERR "Please specify a directory to write to.\n";
  &usage;
  die;
}

if (!-e $ORPLISTDIR) {
  print STDERR "ERROR: $ORPLISTDIR does not exist.\n";
  &usage;
  die;
}

if (!-d $ORPLISTDIR) {
  print STDERR "ERROR: $ORPLISTDIR exists but is not a directory.\n";
  &usage;
  die;
}

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

# clear out the old list files
unlink <$ORPLISTDIR/*>;

# this one is different. Instead of a single report file, we generate
# per-course mailing list files under $ORPLISTDIR
# this query excludes the research and thesis classes 
my $courses_query = $dbh->prepare("select distinct cid from enrolled where cid !~ '^[6-9]9[09]' and cid !~ '[1-3]9[0-9]\$' and type != 'I' order by cid");
#my $courses_query = $dbh->prepare("select distinct cid from enrolled where cid !~ '^[6-9]9[09]' and cid !~ '^6[89][12]' and cid !~ '[1-3]9[0-9]\$' and type != 'I' order by cid");
$courses_query->execute;

# who to put on as people
# instructors, tas, and all addresses (both CS and offsite) get put on
my $instructor_query = $dbh->prepare("select p.login, p.firstname, p.lastname from csorp.classes c, csdb.people p where c.cid = ? and c.instructor = p.login and p.login is not null order by p.login");
my $ta_query = $dbh->prepare("select p.login, p.firstname, p.lastname from csorp.tas t, csdb.people p where t.cid = ? and t.login = p.login and p.login is not null order by p.login");
my $students_cs_query = $dbh->prepare("select p.login, p.firstname, p.lastname from csorp.enrolled e, csdb.people p where e.cid = ? and e.status != 'D' and e.univid = p.univid and p.login is not null and not exists (select * from csdb.preferred_address x where x.login = p.login) order by p.login");
my $students_preferred_query = $dbh->prepare("select p.login, a.email_address, p.firstname, p.lastname from csorp.enrolled e, csdb.people p, csdb.preferred_address a where e.cid = ? and e.status != 'D' and e.univid = p.univid and p.login is not null and a.login = p.login order by p.login");
my $students_offsite_query = $dbh->prepare("select a.email_address, p.firstname, p.lastname from csorp.enrolled e, csdb.people p, csdb.offsite_addresses a where e.cid = ? and e.status != 'D' and e.univid = p.univid and p.login is not null and a.login = p.login");

# process the courses
while (my $row = $courses_query->fetchrow_hashref) {
  my $cid = $row->{cid};

  # if the cid ends in 000, the course is a lecture course, create one
  # list for the course number. if the cid does not end in 000, it's a
  # course with separate sections like 302, generate one list per section
  $cid =~ /^(\d{3})(\d{3})(\d{3})$/;
  my $course = int $1;
  my $section = ($3 eq "000") ? $2 : $3;
  $section = int $section;
  my $LIST = "CS" . $course . "-" . $section . "LIST";

  my @members = (); # addresses of people who are actually on the list
  my @posters = (); # addresses that have post-only ability to the list

  $instructor_query->execute($cid);
  while (my $row2 = $instructor_query->fetchrow_hashref) {
    push @members, "$row2->{login}\@cs.wisc.edu $row2->{firstname} $row2->{lastname}\n";
  }

  $ta_query->execute($cid);
  while (my $row2 = $ta_query->fetchrow_hashref) {
    push @members, "$row2->{login}\@cs.wisc.edu $row2->{firstname} $row2->{lastname}\n";
  }

  $students_cs_query->execute($cid);
  while (my $row2 = $students_cs_query->fetchrow_hashref) {
    push @members, "$row2->{login}\@cs.wisc.edu $row2->{firstname} $row2->{lastname}\n";
  }

  $students_preferred_query->execute($cid);
  while (my $row2 = $students_preferred_query->fetchrow_hashref) {
    push @members, "$row2->{email_address} $row2->{firstname} $row2->{lastname}\n";
    push @posters, "$row2->{login}\@cs.wisc.edu $row2->{firstname} $row2->{lastname}\n";
  }

  $students_offsite_query->execute($cid);
  while (my $row2 = $students_offsite_query->fetchrow_hashref) {
    push @posters, "$row2->{email_address} $row2->{firstname} $row2->{lastname}\n";
  }  

  # print out the actual files
  open LIST, "> $ORPLISTDIR/$LIST"
    or die "Couldn't create $LIST mailing list: $!";
  foreach my $member (@members) {
    print LIST $member;
  }
  close LIST;

  if (scalar @posters > 0) {
    open POSTERS, "> $ORPLISTDIR/$LIST.POSTERS"
      or die "Couldn't create $LIST posters list: $!";
    foreach my $poster (@posters) {
      print POSTERS $poster;
    }
    close POSTERS;
  }
}

# clean up
$courses_query->finish;
$instructor_query->finish;
$ta_query->finish;
$students_cs_query->finish;
$students_preferred_query->finish;
$students_offsite_query->finish;
$dbh->disconnect;
