in reply to Re: Printing out of order...
in thread Printing out of order...
The hashes do contain data. The buffering may be something to look at though not sure where to start for that.#!/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; }
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^3: Printing out of order...
by ikegami (Patriarch) on Mar 05, 2009 at 07:22 UTC | |
|
Re^3: Printing out of order...
by chromatic (Archbishop) on Mar 05, 2009 at 09:15 UTC |