#!/usr/bin/perl

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

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

my $output_file = 'Takes_by_hour.txt';

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


my $start_date = '2009-10-04';
my $end_date = '2009-10-06';

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

open OUTPUT, ">$output_file";
select(OUTPUT); $| = 1;
select(STDOUT); $| = 1;
		
while (my ($host, $default_db) = each(%hosts)) {
	my %take_counts = ();
	
	#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";
		
		#Get List of Instructor with active/current courses
		my $dbh = DBI->connect("DBI:mysql:database=$database:host=$host",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
		my $sql = "SELECT DATE_FORMAT(ar.StartedAt,'%b%d-%k') AS DateTaken, COUNT(ar.UserID) 
			   FROM Assignments AS a JOIN AssignmentResults AS ar ON a.ID=ar.AssignmentID 
			   WHERE DATE(ar.StartedAt) BETWEEN '$start_date' AND '$end_date' GROUP BY DateTaken";
		my $sth = $dbh->prepare ( $sql );
		$sth->execute();

		while (my @row = $sth->fetchrow_array()) {
			if (exists $take_counts{$row[0]}) { 
				$take_counts{$row[0]} += $row[1];
			}
			else { 
				$take_counts{$row[0]} = $row[1];
			}
		}
		$sth->finish();
		$dbh->disconnect();
	}
	foreach (my ($key, $value) = each (%take_counts)) {
		print OUTPUT "$host\t$key\t$value\n";
	}

}

close OUTPUT;