#!/usr/bin/perl

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

######################
#### CONFIG VARS #####
######################
my %hosts = (
	'blah1' => 'stuff1',
	'blah2' => 'stuff2',
	'blah3' => 'stuff3',
	'blah4' => 'stuff4',
	'blah5' => 'stuff5'
);

my $db_user = 'notthis';
my $db_pass = 'definatlynotthis';

my $output_file = "registrations_by_discipline_Top10_with_contacts.txt";

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

my $Abbr="'accounting','busstat','biology','buslaw','health','anthro','nutr','physics','astron','psych'";

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

open OUTPUT, ">$output_file" or die $!;

#get unique list of disciplines
my @disciplines;
my $dbh = DBI->connect("DBI:mysql:database=notthis:host=blah1","fubar","fubard",{RaiseError=>1})|| die "$DBI::errstr\n";
my $sql = "SELECT DISTINCT Discipline FROM BookList WHERE Discipline IN ($Abbr) ORDER BY Discipline";
my $sth = $dbh->prepare ( $sql );
$sth->execute();
while (my $discipline = $sth->fetchrow_array()) {
	push (@disciplines, $discipline);
}
$sth->finish();

foreach my $discipline (@disciplines) {
	print "$discipline\n";
	#get unique list of books for discipline
	my @books;
	my $sql = "SELECT Abbr FROM BookList WHERE Discipline = '$discipline' AND Enabled = '1' AND BookClass = 'SQL' AND Abbr NOT REGEXP '_demo\$'";
	my $sth = $dbh->prepare ( $sql );
	$sth->execute();
	while (my $abbr = $sth->fetchrow_array()) {
		push (@books, "'$abbr'");
	}
	$sth->finish();
	my $books = join (",", @books);
	#get unique list of bundle codes associated with book list
	my @bundles;
	{
		my $sql = "SELECT DISTINCT GroupFeatureCode FROM GroupedFeatures WHERE SUBSTRING(FeatureCode,11) IN ($books)";
		my $sth = $dbh->prepare ( $sql );
		$sth->execute();
		while (my $bundle = $sth->fetchrow_array()) {
			push (@bundles, "'$bundle'");
		}
		$sth->finish();
	}
	#create master book/bundle list
	my @book_list;
	push (@book_list, @books, @bundles);
	my $book_list = join (",",@book_list);
	my $discipline = get_discipline_name($discipline);
	print OUTPUT "$discipline\n";
	

	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 "\t";
			print join ("/", $host, $database) . "\n";
			my $dbh1 = DBI->connect("DBI:mysql:database=$database:host=$host",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
			
			#Get InstructorID
			my $sql = "SELECT DISTINCT u.ID, i.Name, i.City, i.State, CONCAT(u.FirstName,' ',u.LastName), u.EMail, DATE(from_unixtime(u.LastLoginAt/1000)) 
				   FROM $default_db.Institutions AS i JOIN Users AS u ON i.ID=u.InstitutionID JOIN AccessRights AS ar ON u.ID=ar.UserID
				   WHERE (SUBSTRING_INDEX(ar.Feature,'book-view-',-1) OR SUBSTRING_INDEX(ar.Feature,'bun_',-1)) IN ($book_list) AND u.Parent REGEXP '2|3'
				   AND (u.Name NOT REGEXP '$domain_filter' OR u.EMail NOT REGEXP '$domain_filter') AND u.InstitutionID NOT IN ($blocked_institutions)";
				   
			my $sth = $dbh1->prepare ( $sql );
			$sth->execute();
			while (my @row = $sth->fetchrow_array()) {
				my $UserID=$row[0];
				my @SectionIDs;
				
				my $StudentCount = 0;
				my %InstructorTotal = ();

				#Get SectionIDs which use content from $book_list for instructor
				{
					my $book_list_temp = join ("|",@books);
					my $sql = "SELECT s.ID FROM Sections AS s JOIN Assignments AS a ON s.ID=a.SectionID JOIN Tests AS t ON a.AssignmentTestID=t.ID
						   WHERE (a.AssignmentData REGEXP '$book_list_temp' OR t.IData REGEXP '$book_list_temp') AND s.OwnerID='$UserID'";
					my $sth = $dbh1->prepare ( $sql );
					$sth->execute();
					while (my @row = $sth->fetchrow_array()) {
						$SectionIDs = push(",",$row[0]);
					}
				}
				#Get student count for all sections which use $book_list
				{
					my $sql = "SELECT COUNT(sm.UserID) FROM SectionMembers AS sm JOIN Sections AS s ON s.ID=sm.SectionID 
					           WHERE sm.UserType='1' AND (s.ID IN ($SectionIDs) OR s.Parent IN ($SectionIDs))";
					my $sth = $dbh1->prepare ( $sql );
					$sth->execute();
					while (my @row = $sth->fetchrow_array()) {
						$StudentCount = $row[0] ? $row[0] : 0;
					}
				}
				#Add InstructorID (Unique) to Hash
				$InstructorTotal{$UserID} = $StudentCount;



			}
			$sth->finish();
			$dbh1->disconnect();
		}
	}
}	

$dbh->disconnect();
close OUTPUT;