Here's the entire script. Some elements where deleted due to security reasons.
#!/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_pa
+ss);
@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=$hos
+t",$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_d
+b.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 $defau
+lt_db.AccessRights AS ar JOIN Users AS u ON ar.userID=u.ID JOIN $defa
+ult_db.Institutions AS i ON ar.InstitutionID=i.ID
WHERE (CONVERT(ar.LastModified, Date) BETWEEN '$st
+art_date' AND '$end_date') AND (CONVERT(u.CreatedAt,Date) BETWEEN '$s
+tart_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', SU
+BSTRING_INDEX(SUBSTRING_INDEX(DatabaseURL, '/', -1), '?', 1)) AS Data
+baseURL 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;
}
The hashes do contain data. The buffering may be something to look at though not sure where to start for that. |