#!/usr/bin/perl

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

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

my $output_file = 'Algo_usage.txt';

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

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

my $ignored_text = "'sech05h','gbts01t','smhb07e','QAITtb01','qait33','qa'";

######################
#### MAIN BODY #######
######################
select(STDOUT); $| = 1;
open OUTPUT, ">$output_file";
		
select(STDOUT); $| = 1;

my @book_db = get_book_databases();
foreach my $bookdb (@book_db) {
	print "Processing on: $bookdb\n";
	my $dbh = DBI->connect("DBI:mysql:database=$bookdb:host=xxxxx",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
	my $sql = "SELECT COUNT(ID) FROM Items WHERE Content REGEXP '<value name=\"algorithm\">var'";
	my $sth = $dbh->prepare ( $sql );
	$sth->execute();

	while (my @row = $sth->fetchrow_array()) {
		if ($row[0] > 0) {
			my $count = $row[0] ? $row[0] : 0;
			#Get Book Information
		 	{
		 		my $dbh1 = DBI->connect("DBI:mysql:database=xxxx:host=xxxx",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
				my $sql = "SELECT Name, Abbr, Discipline FROM BookList WHERE SUBSTRING(DatabaseURL,19) = '$bookdb'";
				my $sth = $dbh1->prepare ( $sql );
				$sth->execute();

				while (my @row = $sth->fetchrow_array()) {
					print OUTPUT "$row[2]\t$row[0]\t$row[1]\t$count\n";
				}
				$sth->finish();
				$dbh1->disconnect();
			}
		}
	}
	$sth->finish();
	$dbh->disconnect();
}	

close OUTPUT;

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

sub get_book_databases {
	my @book_database_urls;
	my $dbh = DBI->connect("DBI:mysql:database=xxxx:host=xxxxx",$db_user,$db_pass,{RaiseError=>1})|| die "$DBI::errstr\n";
	my $sql = "SELECT DISTINCT SUBSTRING(bl.DatabaseURL,19) FROM BookList AS bl WHERE BookClass='SQL' AND Product IN ('BCTest','TestBank','Forms','Homework','PrintedTestBank') AND Enabled='1' AND bl.Abbr NOT IN ($ignored_text)";
	my $sth = $dbh->prepare ( $sql );
	$sth->execute();
	while (my @row = $sth->fetchrow_array()) {
		print "Testing $row[0] => ";
		if (my $dbh1 = DBI->connect("DBI:mysql:database=$row[0]:host=xxxxx",$db_user,$db_pass,{RaiseError=>1})) {
			print "SUCCESS\n";
			push (@book_database_urls, $row[0]);
		}
		else { print "FAILED\n" }
	}
	$sth->finish();
	$dbh->disconnect();
	return @book_database_urls;
}