#!/usr/bin/perl
# rt-updatedb.pl
#   Copyright Worcester College 2006
#   This script written by Michael Howe (michael.howe@worc.ox.ac.uk) for Worcester
#     College.
#   Released under the GNU GPL v2.
#   Version: $Id: rt-updatedb.pl 61 2006-02-06 22:56:21Z michael $
#
# Usage
#   Try 'perldoc rt-updatedb.pl', or read the bottom of the script.
#

use strict;
use warnings;
use Getopt::Long;
use DBI;
use File::Basename;
# Import RT lib:
use lib "/usr/share/request-tracker3.4/lib";
use RT::Interface::CLI qw(CleanEnv);
use RT::User;
use RT::Group;

#
# These variables should be updated appropriately, and can also be set via the
# command line.
#
# Database type:
my $emaildb_type = 'mysql';	# should be a dbi driver name, eg mysql or Pg
# Database host:
my $emaildb_host = 'localhost';
# Database name:
my $emaildb_name = 'emaildb';
# Database table that contains user data:
my $emaildb_table = 'email';
# User with permission to access the database:
my $emaildb_user = '';
# Password for above user:
my $emaildb_pass = '';

my $rtdb_type = 'mysql';
my $rtdb_host = 'localhost';
my $rtdb_name = 'rtdb';
my $rtdb_user = '';
my $rtdb_pass = '';
# Array of users to ignore (not add to RT):
my @ignore_users = qw( itss0044 );
# Array of email addresses to ignore:
my @ignore_emails = qw( stuart.sharp laurence.kinsella );
# Example:
# my @ignore_emails = qw( stuart.sharp@worc.ox.ac.uk it-support@worc.ox.ac.uk);

# File with usernames/emails to ignore, one per line
my $ignore_file = '';

# Hash of groups.  Each key is the 'status' from the email database table.
#  The value of $groupmap{$key} is the RT group name to add the users to.
my %groupmap = (
	undergrad => 'JCR',
	postgrad => 'MCR',
	college => 'STAFF',
	senmem => 'SCR',
);
# Print output?
my $verbose = 0;
# You don't need to change this.
my $help = 0;
# This should be set to make it execute.
my $execute = 0;
#
# End variables that should need changing
#

# Other variables:
my $procname = basename($0);
my $SystemUser;
my $CurrentUser;
my $CurrentGroup;

#
# resetrt
#   This sets the RT objects up to be suitable to work with.
#
sub resetrt {
	CleanEnv();
	RT::LoadConfig();
	RT::Init;
	$SystemUser =  RT::CurrentUser->new();
	$SystemUser->LoadByName('RT_System');
	$CurrentUser = RT::CurrentUser->new();
	$CurrentGroup = RT::Group->new($SystemUser);
}

#
# checkgroups
#   This checks that the RT groups in the %groupmap hash actually exist, and
#   complains (and deletes the groupmap item).
#
sub checkgroups {
	# Loop through all of the statuses
	foreach ( keys( %groupmap ) ) {
		# Attempt to load the group that goes with the status:
		my $group = $CurrentGroup->LoadUserDefinedGroup( $groupmap{$_});
		# If it can't get the ID of the group, we assume that group
		# doesn't exist and delete it.
		unless ( $CurrentGroup->id =~ m{(\d+)} ) {
			print "Group $groupmap{$_} doesn't exist.\nNot adding people with the status '$_'\n";
			delete( $groupmap{$_} );
		}
	}
}

#
# verbose
#   If the verbose flag is set this prints out more info.
#
sub verbose(@) {
	print ("$procname: ", @_, "\n") if ($verbose);
}

#
# usage
#   Prints a usage statement.
#
sub usage {
	print <<EOF;
$0: updates the requesttracker database with a list of users from the emaildb.

Options:
	--help		Display this message
	--verbose	Display more info on what the script is doing
	--execute	NEW: use this flag to make the command execute (helps to
			prevent mistakes)
	--emaildb_host	Host where the database is running.  Defaults to
			localhost
	--emaildb_type	Type of database (MYSQL, Postges, etc).  For MYSQL use 
			'mysql', for Postgres use 'Pg'.
	--emaildb_name	Set email database
	--emaildb_table	Set email table
	--emaildb_user	Set email database username
	--emaildb_pass	Set email database password
	--ignore_user	Sets a username to not include.  Can be used multiple
			times
	--ignore_email	Sets an email address to not include.  Can be used
			multiple times.  Will assume domain of worc.ox.ac.uk if
			not specified
	--ignore_file	File with usernames (no @) and/or _fully qualified_
			email addresses to ignore
	--group	database_status=RT_group
			Sets an RT group to assign people with the set database
			status to.  Can be used multiple times.  Multiple
			database statuses can be assigned to the same RT group

Note these options can also be changed in the script.

Example usage:

$0 --verbose --ignore_email stuart.sharp --ignore_user itss0044 \ 
--group undergrad=JCRUsers --group postgrad=MCRUsers --group senmem=StaffUsers \
--group staff=StaffUsers

Will verbosely ignore stuart.sharp\@worc.ox.ac.uk and itss0044.  Will add all 
undergrads to the JCRUsers group; all postgrads to the MCRUsers group; and all
staff and senmems to the StaffUsers group.

EOF
}

# This code needs to come first.
# It grabs the options and sets variables accordingly.
# See 'perldoc Getopt::Long' for details of how it works.
GetOptions(
	'help|h' =>		\$help,
	'verbose|v' =>		\$verbose,
	'execute|e' =>		\$execute,
	'emaildb_type=s' =>	\$emaildb_type,
	'emaildb_host=s' =>	\$emaildb_host,
	'emaildb_name=s' =>	\$emaildb_name,
	'emaildb_table=s' =>	\$emaildb_table,
	'emaildb_user=s' =>	\$emaildb_user,
	'emaildb_pass=s' =>	\$emaildb_pass,
	'ignore_user=s' =>	\@ignore_users,
	'ignore_email=s' =>	\@ignore_emails,
	'ignore_file=s' =>	\$ignore_file,
	'group=s' =>		\%groupmap,
);


# Check we don't just want help:
if ($help or not $execute) {
	usage();
	exit(0);
}

# Set up RT.
resetrt();

#  Check the groups we've been given.
#  Afterwards reset our RT objects, just in case.
checkgroups();
#resetrt();

# Grab the ignore list from a file if it exists:
if ($ignore_file) {
	# We _should_ die if the file can't be found, because otherwise we may
	# add users that really _shouldn't_ exist.
	( -f $ignore_file ) or die("Can't read the ignore file '$ignore_file': $!\n");
	# Again, die if we can't open it.
	open( IF, $ignore_file ) or die("Can't open the ignore file '$ignore_file': $!\n");
	
	# Loop through each line of the file.
	while (<IF>) {
		# Remove newlines
		chomp;
		# Don't want blank lines
		next if ( m{^$} );
		# If we've got an @ followed by a period, it's an email address.
		# Even if it's not supposed to be.
		if ( $_ =~ m{@.*\..*} ) {
			verbose("Adding '$_' to email addresses to ignore");
			push @ignore_emails, $_;
		} else {
			# If it's not an email address it's a username
			verbose("Adding '$_' to usernames to ignore");
			push @ignore_users, $_;
		}
	}
	# Close the handle.  But if we can't, don't die.
	close(IF) or print "Can't close '$ignore_file' ($!) - continuing anyway\n";
}


# Make it allow comma-separated ignores too, just in case.
# This allows the format --ignore_user foo,bar,baz as well as --ignore-user foo
# --ignore_user bar --ignore-user baz
# Not widely publicised, but just in case, as other apps do it.
@ignore_users = split(/,/, join(',',@ignore_users));
@ignore_emails = split(/,/, join(',',@ignore_emails));

#
# Email database query.
#

	# This works for $email_type=mysql and Pg.
	my $edbh = DBI->connect("dbi:$emaildb_type:database=$emaildb_name;host=$emaildb_host", "$emaildb_user", "$emaildb_pass", {
		RaiseError => 1,
		}) or die "Can't connect to db '$emaildb_name': $!\n";
	# Create the sql to select statuses (surround each status with single
	# quotes, and separate with commas:
	my $statuses = "'" . join( "', '", keys( %groupmap ) ) . "'";
	# Hash to say what emails/users we _don't_ want to select
	my %ignore = (
		emails => '',
		users => '',
	);

	# Create the SQL to ignore emails
	if ( @ignore_emails ) {
		$ignore{emails} = "AND e.email NOT IN ( '";
		foreach ( @ignore_emails ) {
			# Auto-complete to @worc.ox.ac.uk if there's no @
			# Note that this works on command-line set emails only -
			# because emails set from file must be fully-qualified.
			$_ .= "\@worc.ox.ac.uk" if ( $_ !~ m{@} );
		}
		# Same trick as with $statuses above.
		$ignore{emails} .= join( "', '", @ignore_emails) . "' )";
		verbose( "Ignoring email addresses '" . join( " '", @ignore_emails ) . "'\n" );
	}

	# Create the sql to ignore usernames
	if ( @ignore_users ) {
		$ignore{users} = "AND e.username NOT IN ( '" . join( "', '", @ignore_users) . "' )";
		verbose( "Ignoring usernames '" . join( " '", @ignore_users ) . "'\n" );
	}
	# Build the query
	my $esql = "SELECT
		e.username,
		e.email,
		LEFT(e.bod_id, 7) AS bod_id,
		e.surname,
		e.firstname,
		e.status FROM $emaildb_table AS e 
		WHERE e.status IN ($statuses)
		$ignore{users}
		$ignore{emails}
		ORDER BY e.username
		;";
	# Prepare and execute the query
	my $equery = $edbh->prepare( $esql );
	$equery->execute();
	# Get a reference to an array of the results of the query
	my $array_ref = $equery->fetchall_arrayref( {
		username => 1,
		email => 1,
		bod_id => 1,
		surname => 1,
		firstname => 1,
		status => 1 }
	);
#
# End of email database query.
#

my ($uid, $uname, $rtn, $msg);


# Loop through each row returned from the database, and check their user details
# against the RT database.
my $rdbh = DBI->connect("dbi:$rtdb_type:database=$rtdb_name;host=$rtdb_host",
"$rtdb_user", "$rtdb_pass", {
		RaiseError => 1,
		AutoCommit => 1,
		}) or die "Can't connect to db '$rtdb_name': $!\n";
my $sth = $rdbh->prepare("UPDATE Users SET Name = ? WHERE EmailAddress = ?");

foreach my $row (@$array_ref) {
	unless ( ( $row->{username} ) && ( $row->{email} ) && ($row->{bod_id})){
		print "User '$row->{username}' (email '$row->{email}', bod_id '$row->{bod_id}') is being ignored";
		next;
	}
	print "Updating " . $row->{username} . " from " . $row->{email} . "\n";
	$sth->execute( $row->{username}, $row->{email} );
}
#$rdbh->commit;

# Loop through each row returned from the database and try to add/update RT.
foreach my $row (@$array_ref) {
	# Sanity check - if username, email, or bod_id isn't set for a user,
	# we'll skip them and print a warning.
	unless ( ( $row->{username} ) && ( $row->{email} ) && ($row->{bod_id})){
		print "User '$row->{username}' (email '$row->{email}', bod_id '$row->{bod_id}') is being ignored";
		next;
	}
	$uname = '';
	# Check if the user already exists.  While this will slow down the first
	# run, as they won't exist, it should make successive runs faster.
	( $rtn, $msg ) = $CurrentUser->LoadByEmail( $row->{email} );
	if ( 'Found Object' eq $msg ) {
		$uid = $CurrentUser->PrincipalId;
		$uname = $CurrentUser->Name;
		if ( $uname ne $row->{username} ) {
			print "Error: user $row->{username} has a RT username of'$uname' when it should be $row->{username}.  Ignoring them.\n";
			next;
		}
		verbose( "Setting $row->{username}'s password to $row->{bod_id}");
		$CurrentUser->UserObj->SetDisabled(0);
		my ( $prtn, $pmsg ) = $CurrentUser->UserObj->SetPassword( $row->{bod_id} );
		print "prtn: $prtn\tpmsg: $pmsg\n";
		$CurrentUser->UserObj->SetPrivileged(0);
	} else {
		# User doesn't exist - let's go create them!
		verbose("User " . $row->{'username'} . " does not exist, attempting to create");
		my $new_entry = new RT::User($SystemUser);
		verbose("\t$row->{username}\t$row->{bod_id}\n" );
		( $uid, $msg ) = $new_entry->Create(
					Name => $row->{username},
					Password => $row->{bod_id},
					EmailAddress => $row->{email},
					RealName => "$row->{firstname} $row->{surname}",
				);
		# If it went right, $uid will be non-zero.
		if ( $uid ) {
			verbose("User " . $row->{'username'} . " created successfully (uid $uid).");
		} else {
			warn "(Error: $msg)\n";
		}
	}
	my $grouploaded = $CurrentGroup->LoadUserDefinedGroup( $groupmap{$row->{'status'}} );
	if ( 
		(	( 'Found Object' eq $grouploaded ) ||
			( 'Fetched from cache' eq $grouploaded )
		) && ( $uid )
	) {
		# Add user to group
		# If they are already a member, remove them from the group and
		# re-add them.
		my ( $return, $gmsg ) = $CurrentGroup->AddMember( $uid );
		if ( 'Group already has member' eq $gmsg ) {
			# return = 0 (ie unsuccessful), but we don't mind
			verbose( "User " . $row->{'username'} 
			. " is already a member of group " 
			. $groupmap{$row->{'status'}} );
			# Attempt to delete user from group:
			my ( $delrtn, $delmsg ) = $CurrentGroup->DeleteMember( $uid );
			if ( $delrtn and 'Member deleted' eq $delmsg ) {
				# Deletion was successful.
				verbose( "Removed user from group." );
				# Attempt to add user to group again
				my ( $addrtn, $addmsg ) = $CurrentGroup->AddMember( $uid );
				if ( $addrtn and 'Member added' eq $addmsg ) {
					# All was successful - let the user know
					verbose( "User added to group "
					. "successfully" );
				} else {
					# We couldn't readd them.  Let the user
					# know about this.
					verbose( "Couldn't add user '"
					. $row->{'username'} . " to group '"
					. $groupmap{$row->{'status'}}
					. "' successfully - returned code "
					. "'$addrtn', message '$addmsg'" );
				}
			} else {
				# We couldn't delete the user.
				verbose( "Couldn't remove user from group "
				. "successfully - returned code "
				. "'$delrtn', message '$delmsg'" );
			}
		} elsif ( 0 == $return ) {
			# no success
			warn "(Error: $gmsg)"
		}
	} elsif ( $uid ) {
		# User's all good, but group isn't.
		# This _shouldn't_ happen, thanks to the checkgroups() function,
		# but you never know.
		warn "(Error: Group ". $groupmap{$row->{'status'}}. " could not be loaded for user " . $row->{'username'} . ")\n";
		verbose( "Reason for error: '$grouploaded'" );
	}
	
}


# Cleanup.
my $rc = $edbh->disconnect or print $edbh->errstr;

#
# And here is the end of the script.  Thank you for flying with perl, we hope
# you enjoyed it.
# Below is documentation in the POD format (man perldoc; perldoc rt-updatedb.pl)
#

=head1 Name

rt-updatedb.pl - script to add RT users from an email database

=head1 SYNOPSIS

B<rt-updatedb.pl> [B<--help>] [B<--verbose>]
[B<--emaildb_host <hostname>>]
[B<--emaildb_type <database_type>>]
[B<--emaildb_name <emaildb_name>>]
[B<--emaildb_table <emaildb_table>>]
[B<--emaildb_user <user>>]
[B<--emaildb_pass <pass>>]
[B<--ignore_user <username>>]
[B<--ignore_email <emailaddress>>]
[B<--ignore_file <file>>]
[B<--group [database_status=RT_group] [database_status=RT_group]>]

=head1 DESCRIPTION

I<rt-updatedb.pl> will create users for Request Tracker given the data from a
MYSQL database with email addresses, usernames, statuses, names and university
card numbers.

=head1 OPTIONS

=over 5

=item B<--help>

Prints out a statement on how to use the script.

=item B<--verbose>

Describes what the script is actually doing

=item B<--emaildb_host> I<hostname>

The host that the database is running on.  Note that this file must be run on
the host that RT is running on.

=item B<--emaildb_type> I<type>

The database type, specified as a valid perl driver name.  Defaults to mysql.

 Driver names:
	Database	Perl Driver
	---------------------------
	MYSQL		mysql
	Postgres	Pg

=item B<--emaildb_name> I<database_name>

The name of the MYSQL database that contains the email data.

=item B<--emaildb_table> I<table_name>

The table in the database that contains the email data.

=item B<--emaildb_user> I<user_name>

The MYSQL user that has permissions to read the L<--emaildb_table|/--emaildb_table table_name> table.

=item B<--emaildb_pass> I<password>

Password for the MYSQL user from L<--emaildb_user|/--emaildb_user user_name>.  Note that this is
I<INSECURE> if you do not trust all the users on your machine.  If in doubt,
specify it in the script instead.

=item B<--ignore_user> I<username>

A username to ignore when fetching the email lists, so the user is not readded
to request tracker.  Probably best used to exclude IT support staff.

Example:

	rt-updatedb.pl --ignore_user stuarts

Would ignore the person with the username stuarts.

Can be used multiple times to specify multiple people to ignore.

=item B<--ignore_email> I<email address>

Like L<ignore_user|/--ignore_user username>, this is a username to ignore when fetching the email
lists, so the user is not readded to request tracker.  It will qualify any
addresses given without domain (and @ symbol) to @worc.ox.ac.uk Probably best used to exclude it support staff.

Like L<--ignore_user|/--ignore_user username>, can be used multiple times to
specify multiple people to ignore.

=item B<--ignore_file> I<filename>

Will read a list of usernames and/or fully-qualified email addresses that should
be ignored from I<filename>.  Will die if file cannot be read.

Example file:

	jcr-support@worc.ox.ac.uk
	
	stuarts
	
	itss0044

Would ignore the address jcr-support@worc.ox.ac.uk and the users stuarts and
itss0044.


=item B<--group> I<status>=I<RT_Group>

Allows you to set the Request Tracker group for all people with a particular
status.  rt-updatedb.pl will not process people who aren't listed as being in
one of these groups.

Example:
	rt-updatedb.pl --group undergrad=JCR --group postgrad=MCR --group senmem=STAFF --group college=STAFF

=back

=head1 EMAIL DATABASE STRUCTURE

The email database should have a table with the following fields: username (eg
itss0044), email (eg it-support@worc.ox.ac.uk), bod_id (will take the FIRST
SEVEN digits, surname, firstname, status.  Each user with a status listed in the
%groupmap hash (taken from L<--group|/--group status=RT_Group>) will be selected.

=head1 RT SETUP

Request Tracker should be configured to give all groups listed as RT_Groups
(L<--group|/--group status=RT_Group>) the following permissions on the
appropriate queue:

S<CreateTicket, ReplyToTicket, SeeQueue, ShowTicket>

ShowTicketComments could optionally be set; without it requestors cannot see the
comments made on their tickets.

The users are added as 'non-privileged' users, so they see the RT Self-Service
interface, rather than the full-blown interface that 'privileged' users get.

For example, using B<--group undergrad=JCRUsers> the group JCRUsers would need
to exist with the above permissions for the jcr support queue.  When the script
is run, everyone with the 'undergrad' status will be added to the JCRUsers
group, with the ability to see their own tickets, and create new ones.

=head1 BUGS

Users are never removed.  This means that if they move status groups (eg
undergrad -> postgrad), they will be able to access both queues when added.

B<--input_file> does not accept usernames with an '@' symbol; it instead parses
them as email addresses.

There should probably be some way to 'trial' this - ie to do everything _bar_
create the user.

=head1 SECURITY

This is a perl script.  There are almost certainly bugs.  Don't run in an
environment where you don't trust the system and the users, and beware of the
L<--emaildb_pass|/--emaildb_pass password> option, which will show up your password in process lists.

=head1 LICENSE

Released under the GNU GPL v2.
Enjoy.

=head1 AUTHOR

Original creator: Michael Howe <michael.howe@worc.ox.ac.uk>

Created on request for Worcester College L<http://www.worc.ox.ac.uk>.

=head1 COPYRIGHT

Copyright Worcester College 2006

=cut


# vim: tw=80