#!/usr/bin/perl

use strict;
use warnings;
use DBD::mysql;

######################
#### CONFIG VARS #####
######################

#Specify start and end dates to filter data
my $start_date = '2009-02-01';
my $end_date = '2009-02-31';

#Specify blocked institutions
my @blocked_institutions = ();

my $domain_filter = "";

my $output_file = 'Modern_Registration_2-1_to_2-31.txt';

######################
#### PRIVATE VARS ####
######################

my %hosts = ();

my $db_user = '';
my $db_pass = '';

my %RegStudentNew;
my %RegInstructorNew;

my %RegStudentExisting;
my %RegInstructorExisting;

my %StudentLogins;
my %InstructorLogins;

######################
#### MAIN BODY #######
######################

open OUTPUT, ">$output_file";
		
while (my ($host, $default_db) = each(%hosts)) {
	
	#get database urls for host
	my @databases = get_databases($host, $default_db, $db_user, $db_pass);
	@databases = sort(@databases);
	
	#process data on each database
	foreach my $database (@databases) {
		
		print join ("/", $host, $database) . "\n";
		
		#Existing Accounts that added Product(s)
		my $dbh = DBI->connect("DBI:mysql:database=$database:host=$host",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
		my $sql = "SELECT ar.UserID, i.Name, u.Parent FROM $default_db.AccessRights AS ar JOIN Users AS u ON ar.userID=u.ID JOIN $default_db.Institutions AS i ON ar.InstitutionID=i.ID 
			WHERE CONVERT(ar.LastModified, Date) BETWEEN '$start_date' AND '$end_date' AND CONVERT(u.CreatedAt,Date) < '$start_date' ";
		my $sth = $dbh->prepare ( $sql );
		$sth->execute();
		while (my @row = $sth->fetchrow_array()) {
			if ($row[2] == '1') { $RegStudentExisting{$row[1]} += 1 }
 			if ($row[2] == '2') { $RegInstructorExisting{$row[1]} += 1 }
			}
			
			$sql = "SELECT u.ID, u.Parent, i.Name FROM Users AS u JOIN $default_db.Institutions AS i ON u.InstitutionID=i.ID WHERE CONVERT(from_unixtime(u.LastLoginAt/1000), Date) BETWEEN '$start_date' AND '$end_date' "; 
			$sth = $dbh->prepare ( $sql );
			$sth->execute();
			while (my @rows = $sth->fetchrow_array() ) {
				if ($rows[1] == '1') { $StudentLogins{$rows[2]} += 1 }
				if ($rows[1] == '2') { $InstructorLogins{$rows[2]} += 1 }
			}

				$sql = "SELECT ar.UserID, i.Name, u.Parent FROM $default_db.AccessRights AS ar JOIN Users AS u ON ar.userID=u.ID JOIN $default_db.Institutions AS i ON ar.InstitutionID=i.ID
					WHERE (CONVERT(ar.LastModified, Date) BETWEEN '$start_date' AND '$end_date') AND (CONVERT(u.CreatedAt,Date) BETWEEN '$start_date' AND '$end_date') ";
				$sth = $dbh->prepare ( $sql );
				$sth->execute();
				while (my @rows1 = $sth->fetchrow_array() ) {
				if ($rows1[2] == '1') { $RegStudentNew{$rows1[1]} += 1 }
 				if ($rows1[2] == '2') { $RegInstructorNew{$rows1[1]} += 1 }
				}
			
		$sth->finish();
		$dbh->disconnect();	
	}
}


# PRINT RESULTS (Will turn into sr)


print OUTPUT "New Student Registrations\n---------------------------\n";
foreach my $Inst (sort keys %RegStudentNew) {
	if ($RegStudentNew{$Inst} ) {
			print OUTPUT "$Inst\t$RegStudentNew{$Inst}\n"; 
		}
	}


print OUTPUT "\nNew Instructor Registrations\n------------------------\n";
foreach my $Inst (sort keys %RegInstructorNew) {
	if ($RegInstructorNew{$Inst} ) {
			print OUTPUT "$Inst\t$RegInstructorNew{$Inst}\n"; 
		}
	}


print OUTPUT "\nExisting Student Registrations\n-----------------------\n";
foreach my $Inst (sort keys %RegStudentExisting) {
	if ($RegStudentExisting{$Inst} ) {
			print OUTPUT "$Inst\t$RegStudentExisting{$Inst}\n"; 
		}
	}


print OUTPUT "\nExisting Instructor Registrations\n---------------------\n";	
foreach my $Inst (sort keys %RegInstructorExisting) {
	if ($RegInstructorExisting{$Inst} ) {
			print OUTPUT "$Inst\t$RegInstructorExisting{$Inst}\n"; 
		}
	}


print OUTPUT "\nStudent Logins\n----------------------------------------\n";
foreach my $Inst (sort keys %StudentLogins) {
	if ($StudentLogins{$Inst} ) {
			print OUTPUT "$Inst\t$StudentLogins{$Inst}\n"; 
		}
	}


print OUTPUT "\nInstructor Logins\n--------------------------------------\n";
foreach my $Inst (sort keys %InstructorLogins) {
	if ($InstructorLogins{$Inst} ) {
			print OUTPUT "$Inst\t$InstructorLogins{$Inst}\n"; 
		}
	}
	

close OUTPUT;

######################
#### PRIVATE SUBS ####
######################

sub get_databases {
	my @database_urls;
	my ($host, $default_db, $db_user, $db_pass) = @_;
	my $dbh = DBI->connect("DBI:mysql:database=$default_db:host=$host",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
	my $sql = "SELECT DISTINCT IF(DatabaseURL = '.', '$default_db', SUBSTRING_INDEX(SUBSTRING_INDEX(DatabaseURL, '/', -1), '?', 1)) AS DatabaseURL FROM Institutions WHERE WebSiteURL = '.' AND ID != ''";
	my $sth = $dbh->prepare ( $sql );
	$sth->execute();
	while (my @row = $sth->fetchrow_array()) {
		my $url = $row[0];
		push (@database_urls, $url);
	}
	$sth->finish();
	$dbh->disconnect();
	return @database_urls;
}