#!/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 I
+N ($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 = '$discipli
+ne' AND Enabled = '1' AND BookClass = 'SQL' AND Abbr NOT REGEXP '_dem
+o\$'";
    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 GroupedFeatur
+es 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, $d
+b_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.Las
+tLoginAt/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.Paren
+t REGEXP '2|3'
                   AND (u.Name NOT REGEXP '$domain_filter' OR u.EMail 
+NOT REGEXP '$domain_filter') AND u.InstitutionID NOT IN ($blocked_ins
+titutions)";
                   
            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 Ass
+ignments AS a ON s.ID=a.SectionID JOIN Tests AS t ON a.AssignmentTest
+ID=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_li
+st
                {
                    my $sql = "SELECT COUNT(sm.UserID) FROM SectionMem
+bers AS sm JOIN Sections AS s ON s.ID=sm.SectionID 
                               WHERE sm.UserType='1' AND (s.ID IN ($Se
+ctionIDs) 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;
[download]