#!/usr/bin/perl

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

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

#Specify start and end dates to filter data
my $start_date = '2008-12-25';
my $end_date = '2009-04-01';

my $output_file = 'Hordis_Item_Overview.txt';

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

my $db_user = 'xxxxx';
my $db_pass = 'xxxxx';

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

open OUTPUT, ">$output_file";

my $dbh = DBI->connect("DBI:mysql:database=xxxx:host=xxxxx",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";

#Hash to hold ItemUID and Item Name
my %ItemUID= ();

#Get ItemUID and Item Name (ItemUID is unique to each Test.IData)
{
my $sql = "SELECT t.IData FROM Tests AS t JOIN Assignments AS a ON a.AssignmentTestID=t.ID JOIN Users AS u ON u.ID=t.UserID WHERE u.InstitutionID='004452' AND t.IData REGEXP 'ch01.03.MultipleChoice 049';";
my $sth = $dbh->prepare ( $sql );
$sth->execute();

while (my @row = $sth->fetchrow_array()) {
	my $idata = $row[0];
	while ($idata =~ /<item([^>]+)/gi) {
	my $elements = $1;
	my $src;
	my $uid;

	if ($elements =~ /src=\"\w+\/([^"]+)/i) { $src = $1 } 
		if ($elements =~ /uid=\"([^"]+)/i) { $uid = $1 } 
	
	if (not exists $ItemUID{$uid}) { $ItemUID{$uid} = $src }
	}	
}
$sth->finish();
}

#Hold Unique Item Names to use against ItemUID hash
@hvalues=();

while (my ($key, $value) = each $ItemUID) {
	push
#Get Item Specific Info for Each Student/Assignment
{
while (my ($key, $value) = each %ItemUID) {
	print "Processing $key/$value\n";

	my $sql = "SELECT s.Name, CONCAT(u.LastName,', ',u.FirstName), a.Name, TRUNCATE((MAX(air.TimeSpent)/60),0), TRUNCATE((MIN(air.TimeSpent)/60),0), SUM(air.TimesTaken), AVG(air.Score), air.FeedbackAttempts, air.HintsGiven
	   FROM Sections AS s JOIN Assignments AS a ON s.ID=a.SectionID JOIN Users AS u ON u.ID=s.OwnerID JOIN AssignmentResults AS ar ON a.ID=ar.AssignmentID JOIN AssignmentItemResults AS air ON ar.ID=air.AssignmentResultsID 
	   WHERE air.ItemUID IN '$key' GROUP BY s.Name ORDER BY s.Name, a.Name, u.LastName";
	my $sth = $dbh->prepare ( $sql );
	$sth->execute();

	while (my @row = $sth->fetchrow_array()) {
		print OUTPUT "$row[0]\t$row[1]\t$row[2]\t$row[3]\t$row[4]\t$row[5]\t$row[6]\t$row[7]\n";
		}
	$sth->finish();
	}

}




$dbh->disconnect();	

close OUTPUT;

######################
#### PRIVATE SUBS ####
######################