use warnings 'all';
use strict;

use LWP::Simple 'get';
use Spreadsheet::WriteExcel;
use HTML::TreeBuilder;
use Path::Tiny;
use constant URL => 'http://pastebin.com/raw/qLwu80ZW';

my $teamNumber = "";my $teamName="";my $schoolName="";my $area="";my $district=""; my $agDeptPhone=""; my $schoolPhone=""; my $fax=""; my $addressOne=""; my $addressTwo=""; my $city="";
my $state=""; my $zipCode=""; my $name=""; my $email=""; my $row=""; my $Ypos=0;

my $workbook  = Spreadsheet::WriteExcel->new('perlOutput.xlsx');
my $worksheet = $workbook->add_worksheet();

sub getTeamNumber {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_TeamNumber/)->attr('value');
	}
	return $val;
}
sub getTeamName {
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_TeamName/)->attr('value');
	}
	return $val;
}
sub getSchoolName {
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(tag_ => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_SchoolName/)->attr('value');
	}
	return $val;
}
sub getArea{
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(tag_ => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Area/)->attr('value');
	}
	return $val;
}
sub getDistrict{
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_District/)->attr('value');
	}
	return $val;
}
sub getDeptPhone{
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Phone/)->attr('value');
	}
	return $val;
}
sub getSchoolPhone{
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Phone2/)->attr('value');
	}
	return $val;
}
sub getFax{
	my ($file) = @_;
	my $html = path($file)->slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Fax/)->attr('value');
	}
	$val =~ s/\-//g;
	return $val;
}
sub getAddress1 {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Address1/)->attr('value');
	}
	return $val;
}
sub getAddress2 {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Address2/)->attr('value');
	}
	return $val;
}
sub getCity {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_City/)->attr('value');
	}
	return $val;
}
sub getState {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_State/)->attr('value');
	}
	return $val;
}
sub getZip {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Zip/)->attr('value');
	}
	return $val;
}
sub getWebsite {
	my ($file) = @_;
	my $html = path($file)-> slurp;
	my $tree = HTML::TreeBuilder->new_from_content($html);
	my @nodes = $tree->look_down(_tag => 'input');
	my $val;
	foreach my $node (@nodes) {
		$val = $node->look_down('name', qr/\$txt_Website/)->attr('value');
	}
	return $val;
}
sub getNameAndEmail {
my ($file) = @_;
	my $tree = HTML::TreeBuilder->new_from_content(get URL);
	my ($table) = $tree->look_down(_tag => 'table', class => 'rgMasterTable');

	for my $tr ( $table->look_down(_tag => 'tr') ) {
		next unless my @td = $tr->look_down(_tag => 'td');
		my ($name, $email) = map { $_->as_trimmed_text } @td[0,1];
}
	return ($name, $email);
}

#FILLER: This fills the spreadsheet with all the variables we've acquired
sub fill {
	my ($name, $email, $teamNumber, $teamName, $schoolName, $area, $district,
		$agDeptPhone, $schoolPhone, $fax, $addressOne, $addressTwo, $city, $state, $zipCode) = (@_);
	$worksheet->write($Ypos, 1, $name);
	$worksheet->write($Ypos, 2, $email);
	$worksheet->write($Ypos, 3, $teamNumber);
	$worksheet->write($Ypos, 4, $teamName);
	$worksheet->write($Ypos, 5, $schoolName);
	$worksheet->write($Ypos, 6, $area);
	$worksheet->write($Ypos, 7, $district);
	$worksheet->write($Ypos, 8, $agDeptPhone);
	$worksheet->write($Ypos, 9, $schoolPhone);
	$worksheet->write($Ypos, 10, $fax);
	$worksheet->write($Ypos, 11, $addressOne);
	$worksheet->write($Ypos, 12, $addressTwo);
	$worksheet->write($Ypos, 13, $city);
	$worksheet->write($Ypos, 14, $state);
	$worksheet->write($Ypos, 15, $zipCode);
		}

#Open judgingcard directory
opendir(DH, "\/Judging Card\/");
my @files = readdir(DH);

	#This fills out all top row info
	$worksheet->write("A1","Name");
	$worksheet->write("A2","Email");
	$worksheet->write("A3","Team Number");
	$worksheet->write("A4","Team Name");
	$worksheet->write("A5","School Name");
	$worksheet->write("A6","Area");
	$worksheet->write("A7","District");
	$worksheet->write("A8","Ag Dept Phone");
	$worksheet->write("A9","School Phone");
	$worksheet->write("A10","Fax");
	$worksheet->write("A11","Address One");
	$worksheet->write("A12","Address Two");
	$worksheet->write("A13","City");
	$worksheet->write("A14","State");
	$worksheet->write("A15","Zip Code");
	###################################


foreach my $file (@files) { #run through all files in directory
	next if (-d $file); #Skip file if file is folder

		$Ypos = $Ypos + 1;

		my ($name1, $email1) = getNameAndEmail($file);

		$name = $name1;
		$email = $email1;
		$teamNumber = getTeamNumber($file);
		$teamName = getTeamName($file);
		$schoolName = getSchoolName($file);
		$area = getArea($file);
		$district = getDistrict($file);
		$agDeptPhone = getDeptPhone($file);
		$schoolPhone = getSchoolPhone($file);
		$fax = getFax($file);
		$addressOne = getAddress1($file);
		$addressTwo = getAddress2($file);
		$city = getCity($file);
		$state = getState($file);
		$zipCode = getZip($file);


		fill($name, $email, $teamNumber, $teamName, $schoolName, $area, $district, $agDeptPhone, $schoolPhone, $fax, $addressOne,
			$addressTwo, $city, $state, $zipCode);

}
closedir(DH);