#!/usr/bin/perl
#
# Blackboard database sync:  Sync local users with blackboard database
#
# Grabs all the faculty (via ldap) and current students (from database) and
# batch uploads them to the blackboard server.  Note that blackboard just skips
# the uploaded user if it already exists in their database.  We then grab
# those faculty and students whose accounts have expired and batch remove
# them.
#
# +----------------------------------------------------------------------+
# | Copyright (c) 2005 Jason Rust <jrust@brooks.edu>                     |
# +----------------------------------------------------------------------+
# | This source file is subject to the GNU Lesser Public License (LGPL), |
# | that is bundled with this package in the file LICENSE, and is        |
# | available at through the world-wide-web at                           |
# | http://www.fsf.org/copyleft/lesser.html                              |
# | If you did not receive a copy of the LGPL and are unable to          |
# | obtain it through the world-wide-web, you can get it by writing the  |
# | Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, |
# | MA 02111-1307, USA.                                                  |
# +----------------------------------------------------------------------+
# {{{ Config

### LDAP config ###
# The IP or hostname of the LDAP server
my $ldap_server = 'my.ldap.com';
# The dn used for binding to the LDAP server
my $ldap_user = 'CN=user,OU=Users,DC=my,DC=ldap,DC=com';
# The password used for binding to the LDAP server
my $ldap_passwd = 'password';
# The base dn to search
my $ldap_dn = 'OU=Users,DC=my,DC=ldap,DC=com';
# The filter for determining which users to fetch for creating accounts
my $ldap_create_filter = '(&(objectclass=user)(!(msexchhidefromaddresslists=TRUE))(description=Faculty))';
# The attributes used for creating users
my @ldap_create_attrs = ( 'sAMAccountName', 'sn', 'givenName', 'mail' );
# The filter for determining which users to fetch for removing accounts
my $ldap_remove_filter = '(&(objectclass=user)(msexchhidefromaddresslists=TRUE)(description=Faculty))';
# The attributes used for removing users
my @ldap_remove_attrs = ( 'sAMAccountName' );

### Database config ###
# The DSN for the user database
my $dsn = 'DBI:mysql:studentinfo:dbserver';
# The database username
my $db_user = 'username';
# The database passwd
my $db_passwd = 'password';

### Blackboard config ###
# The url to the login page of your BB installation
my $bb_login_url = 'http://school.blackboard.com/webapps/login';
# The url of the "Batch Create Users" page
my $bb_create_url = 'http://school.blackboard.com/bin/admin/batch_users.pl';
# The url of the "Batch Remove Users" page
my $bb_remove_url = 'http://school.blackboard.com/bin/admin/batch_delete_users.pl';
# The username of a BB admin
my $bb_user = 'bbsupport';
# The password of the user
my $bb_passwd = 'password';

# }}}
# {{{ run

use strict;
use Net::LDAP;
use File::Temp;
use DBI;
use WWW::Mechanize;
use MIME::Base64;
upload_users_to_bb();

# }}}
# {{{ upload_users_to_bb()

# Logs on to blackboard and uploads the csv files to create and remove users.
sub upload_users_to_bb()
{
    my $browser = WWW::Mechanize->new(autocheck => 1, quiet => 0);
    $browser->get($bb_login_url);
    $browser->form_number(1);
    $browser->field('user_id', $bb_user);
    $browser->field('encoded_pw', encode_base64($bb_passwd));
    $browser->click();
    die "Could not log in to Blackboard" if ($browser->{'content'} !~ /<b>You are being redirected to another page<\/b>/);

    my $upload_file = create_user_csv("create");
    $browser->get($bb_create_url);
    $browser->form_number(1);
    $browser->field('batch_file', $upload_file);
    $browser->click();

    $upload_file = create_user_csv("remove");
    $browser->get($bb_remove_url);
    $browser->form_number(1);
    $browser->field('batch_file', $upload_file);
    $browser->click();
    die "There was a problem removing old users" if ($browser->{'content'} !~ /This action is final/);
    $browser->form_number(1);
    $browser->click();
}

# }}}
# {{{ create_user_csv()

# Creates a temporary CSV file of users.
#
# The fields blackboard can accept:
# UsernameRequired, Last NameRequired, First NameRequired, EmailRequired, PasswordRequired, Student ID, Middle Name, Job Title, Department, Company, Street 1, Street 2, City, State / Province, Zip / Postal Code, Country, Work Phone, Home Phone, Work Fax, Mobile Phone, Website, Primary Institution Role, System Availability
#
# @param string $action Whether we are getting the users to remove or create
#
# @return string The name of the temp file
sub create_user_csv
{
    my $action = shift;
    my ($fh, $filename) = File::Temp::tempfile('BB-XXXXXX', SUFFIX => '.csv', 'UNLINK' => 1, 'DIR' => '/tmp');
    my @users = get_ldap_users($action);
    foreach my $user_ref (@users) {
        print $fh '"';
        print $fh join '","', @$user_ref;
        print $fh "\"\n";
    }

    @users = get_db_users($action);
    foreach my $user_ref (@users) {
        print $fh '"';
        print $fh join '","', @$user_ref;
        print $fh "\"\n";
    }

    return $filename;
}

# }}}
# {{{ get_ldap_users()

# Get all the LDAP users.
# For create it returns an array of username, last name, first name, email, random passwd
# For remove it returns an array of usernames
#
# @param string $action Whether we are getting the users to remove or create
#
# @return array An array of users
sub get_ldap_users
{
    my $action = shift;
    my ($ldap, $mesg, $entry);
    $ldap = Net::LDAP->new($ldap_server) or die "$@";
    $mesg = $ldap->bind($ldap_user, password => $ldap_passwd);
    die $mesg->error if $mesg->code;
    my @attrs = $action eq "create" ? @ldap_create_attrs : @ldap_remove_attrs;
    my $filter = $action eq "create" ? $ldap_create_filter : $ldap_remove_filter;
    $mesg = $ldap->search(base => $ldap_dn, filter => $filter, attrs => @attrs);
    die $mesg->error if $mesg->code;
    my @result = ();
    srand(time() ^($$ + ($$ <<15))) ;
    foreach $entry ($mesg->entries) { 
        my @tmp_arr = ();
        foreach my $attr (@attrs) {
            push @tmp_arr, $entry->get_value($attr);
        }

        # Add password
        if ($action eq "create") {
            push @tmp_arr, rand();
        }

        push @result, [ @tmp_arr ];
    }

    $mesg = $ldap->unbind;
    return @result;
}

# }}}
# {{{ get_db_users()

# Get all the database users.
# For create it returns an array of username, last name, first name, email, studentID
# For remove it returns an array of username
#
# @param string $action Whether we are getting the users to remove or create
#
# @return array An array of users
sub get_db_users
{
    my $action = shift;
    my ($dbh, $sql, $sth, @row);
    $dbh = DBI->connect($dsn, $db_user, $db_passwd) or die "Could not connect to database: " . DBI->errstr;
    if ($action eq "create") {
        $sql = 'SELECT username, last_name, fst_name, ema, student_
                FROM users
                WHERE status IN ("NDS-ATT", "NDS-R", "NDS-F", "REENT", "FUT", "ATT", "PROB", "RET")';
    }
    else {
        $sql = 'SELECT username
                FROM users
                WHERE status IN ("DROP", "PGRAD", "INC", "CANCEL", "SPN")';
    }

    $sth = $dbh->prepare($sql);
    $sth->execute() or die "Could not execute query: " . $sth->errstr;

    my @result = ();
    srand(time() ^($$ + ($$ <<15))) ;
    while (@row = $sth->fetchrow_array()) {
        # Add password
        if ($action eq "create") {
            push @row, rand();
        }

        push @result, [ @row ];
    }

    $dbh->disconnect;
    return @result;
}

# }}}