#!/usr/bin/perl
use strict;
use CGI::Carp qw/fatalsToBrowser /;

use lib qw(/home/thesite/local_mods);
my $file = '/home/thesite/public_html/1999.xls';


use Spreadsheet::ParseExcel::Simple;

use CGI ':standard';

use CGI::Pretty qw( :html3 );
my $q = CGI->new();

my $top='<TABLE width="450" ><TR><TD ALIGN="LEFT"><P ALIGN="LEFT"><IMG SRC="/images/kidscountlogo.gif" WIDTH=68 HEIGHT=100 BORDER=0 ALT="Kids Count"></P></TD><TD ALIGN="CENTER"><P ALIGN="CENTER"><IMG SRC="/images/kidscount.gif" WIDTH=294 HEIGHT=59 BORDER=0 ALT="Kids Count!"></P></TD><TD ALIGN="RIGHT"><P ALIGN="RIGHT"><IMG SRC="/images/kidscountlogo.gif" WIDTH=68 HEIGHT=100 BORDER=0 ALT="Kids Count"></P></TD></TR></TABLE></CENTER><P ALIGN="CENTER"><FONT SIZE="-1"><A HREF="http://www.agendaforchildren.org">« Home</A><br>If connection is slow, data is mirrored <a href="http://www.nolaflash.com/cgi-bin/kidscount2.pl">here</a>. <br>Use the <A HREF="#foot">Select Box</A> at the bottom of this page to choose a parish.</FONT></P><H3 ALIGN="CENTER">1999 Kids Count Data Book on Louisiana&#039;s Children</H3>';

##row labels not found in xl file
my @r = ("Total","Under 20","Under 5","\% Children under age 18 in poverty","Number of children under age 18 in poverty","Median Family Income(1998)","\% Unemployed (April 1999)","\# Unemployed (April 1999)","FITAP (FY 1998)",
"Medicaid (March 1999)","Food Stamps (November 1998)","WIC (1998)","Child Care Assistance (June 1998)","Child Care Assistance (June 1998)","Live Births","\% Births To Teens","\% Low Birthweight Babies","\% Women Receiving<br>Adequate Prenatal Care","Infant Mortality Rate","\% Up to Date Immunization<br>(Public Clinics 1998)","Children in custody under supervision<br>of Office of Youth Development","Crimes against property","Crimes against persons","Abuse and neglect cases found valid<br>(# of children)","Physical abuse","Neglect","Sexual abuse","Children in Foster Care","Registration","\$ Per Pupil Expenditure","\$ Average Teacher Salary","Special education services","Dropouts");

my @topic_menu = ("Total # Children","Children Under 20","Children Under 5","\% Children in poverty","\# Children in poverty","Median Family Income","\% Unemployed","\# Unemployed","Receiving FITAP","Receiving Medicaid","Receiving Food Stamps","Receiving WIC","Child Care Assistance","CCA FINDWORK","Live Births","\% Births To Teens","\% Low Birthweight Babies","\% Adequate Prenatal Care","Infant Mortality Rate","\% Immunization","Children in OYD custody","Crimes against property","Crimes against persons","Abuse and neglect cases","Physical abuse","Neglect","Sexual abuse","Children in Foster Care","# Students Registered","\$ Per Pupil","\$ Teacher Salary","Special education","Dropouts");  ## format = starting_index.no_of_elements


my $corner ='#6A5ACD';
my $data = '#dddddd';
my @toprow =  	$q->Tr({-align=>"center"},
				$q->td( {-bgcolor=>"$corner"},"&nbsp;" ),
			  	$q->td( b('White') ),
			  	$q->td( b('African American') ),
			  	$q->td( b('Total (All Races)') ) );

##table titles also not in xl
my @t = ("Population (1997)","Poverty (1995)","Employment and Income","Children Receiving:","Maternal and Child Health (1997)","Children In Trouble (FY 1998)","Child Abuse and Neglect (FY 1998)","Public Education");

my %info;


my ($firstrow, @topics);
my $xls = Spreadsheet::ParseExcel::Simple->read($file);
  foreach my $sheet ($xls->sheets) {
     while ($sheet->has_data) {
     	my @a = $sheet->next_row;
     	my $b = shift(@a);
     	if ($firstrow < 1) {  #don't add topics to hash
     		@topics = @a ; $firstrow++;
     	} else {
     		$info{$b} = \@a;
     	}
     }
  }


my ($i,$j,$k);  #iteration control i=cell data j=row labels k=table titles
my @parishes = sort(keys %info);
my $choice = $q->param('choice') || 'Louisiana';
my $headline;
if ($choice ne 'Louisiana') { $headline = " Parish"; }
if ( $q->param( 'topic' ) ) { by_topic(); }
by_parish();

sub by_parish  {

	print $q->header,start_html( {-title=>"Kids Count Data for $choice$headline"}) ,
		div({-align=>"center"},
		$top, h2({-align=>"CENTER"},"Data for $choice$headline"), hr({-width=>"400"}),
		), #div
		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Population (1997)
			@toprow,
			parishdata_x3(),parishdata_x3(),parishdata_x3(),


			), #table

			br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Poverty (1995)
			@toprow,
			parishdata_x1(),parishdata_x1(),
		), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},   ##Employment and Income
			@toprow,
			parishdata_x1(), parishdata_x1(), parishdata_x1(),
		), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Children Receiving:
			@toprow,parishdata_x1(), parishdata_x1(),parishdata_x3(), parishdata_x3(),
			parishdata_x1(), parishdata_x1(),

		), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Maternal and Child Health (1997)
			@toprow,
			parishdata_x3(), parishdata_x3(),parishdata_x3(), parishdata_x3(),
			parishdata_x3(), parishdata_x1(),
		), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Children In Trouble (FY 1998)
			@toprow,
			parishdata_x3(), parishdata_x1(),parishdata_x1(),
		), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Child Abuse and Neglect (FY 1998)
			@toprow,
			parishdata_x3(), parishdata_x3(),parishdata_x3(), parishdata_x3(),
			parishdata_x3(),
			), #table

		br, br, b($t[$k++]),br,
		table( {-width=>'600',-border=>'1'},  ##Public Education
			@toprow,
			parishdata_x3(), parishdata_x1(),parishdata_x1(), parishdata_x1(),
			parishdata_x1(),
			); #table

			footer();
		exit;

}

sub by_topic {
	my $topic = $q->param( 'topic' );
	chomp $topic;
	my %topic_lookup = ("Total \# Children","0:0:3","Children Under 20","1:3:3","Children Under 5","2:6:3",
	"\% Children in poverty","3:9:1","\# children in poverty","4:10:1","Median Family Income","5:11:1",
	"\% Unemployed","6:12:1","\# Unemployed","7:13:1","Receiving FITAP","8:14:1","Receiving Medicaid",
	"9:15:1","Receiving Food Stamps","10:16:3","Receiving WIC","11:19:3","Child Care Assistance",
	"12:22:1","CCA FINDWORK","13:23:1","Live Births","14:24:3","\% Births To Teens","15:27:3",
	"\% Low Birthweight Babies","16:30:3","\% Adequate Prenatal Care","17:33:3","Infant Mortality Rate",
	"18:36:3","\% Immunization","19:37:1","Children in OYD custody","20:40:3","Crimes against property",
	"21:43:1","Crimes against persons","22:44:1","Abuse and neglect cases","23:47:3","Physical abuse","24:50:3",
	"Neglect","25:53:3","Sexual abuse","26:56:3","Children in Foster Care","27:59:3","\# Students Registered",
	"28:62:3","\$ Per Pupil","29:63:1","\$ Teacher Salary","30:64:1","Special education",
	"31:65:1","Dropouts","32:66:1");  ## format = @topic_menu_element.starting_index_in xl.no_of_xl_elements

	my $display = $topic_lookup{$topic};
	my @numbers = split(/:/, $display);
	my $heading = $numbers[0];
	my $index = $numbers[1];
	my $index2 = $index + 1;
	my $index3 = $index2 + 1;
	my $elements = $numbers[2];

	$_ = 'Louisiana';
	my @cells;
	if ($elements < 2) {
		@cells =  $q->td( {-align=>'left'},  b("$topic") ).
				$q->td( {-bgcolor=>"$data"},"&nbsp;").
				$q->td( {-bgcolor=>"$data"},"&nbsp;").
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index]);
	} else {
		@cells = 		$q->td( {-align=>'left'},  b("$topic") ).
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index]).
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index2]).
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index3] );
	}


	print $q->header,start_html( {-title=>"Kids Count Data for $topic"}) ,
		div({-align=>"center"},
		$top, h2({-align=>"CENTER"},"$topic"), hr({-width=>"400"}),

		); #div
	my $LA = @{$info{'Louisiana'}}[$index];
	delete $info{'Louisiana'};

		print $q->b("$_:"),br,
		table( {-border=>1,-width=>'600'}, Tr({-align=>"center"}, \@toprow ),
		Tr( {-align=>'right', -bgcolor=>"$data"},\@cells ), ), br,
		br,b("Parish Level Data on "), h3("$topic:");
	my @keys;
	#alpha sort
	@keys = sort(keys %info);

	my @rows;

	if ($elements < 2) {
		@rows = map { $q->Tr( {-align=>'right'},
				$q->td( {-align=>'left'},  b("$_ Parish:")),
				$q->td( {-bgcolor=>"$data"},"&nbsp;"),
				$q->td( {-bgcolor=>"$data"},"&nbsp;"),
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index] ) ) } @keys;
	} else {
		@rows = map { $q->Tr( {-align=>'right'},
				$q->td( {-align=>'left'},  b("$_ Parish:") ),
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index]),
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index2]),
				$q->td( {-bgcolor=>"$data"},@{$info{$_}}[$index3]) ) } @keys;
	}
	print $q->p(
			table( {-border=>1,-width=>'600'}, @toprow, @rows ),
			);  #p
		footer();
	exit;
}



sub footer {
	print $q->p("* indicates that parish level data is not available."),
			hr( {-width=>'80%',-align=>"center"}),
			table( {-align=>'CENTER',-width=>"600"},
			  Tr( {-align=>'CENTER'},
			  	td(
					font( {-face=>'Arial', -size=>'3'},"Please select a parish to view:"),
					start_form( {-action=>url,-name=>"by_parish"} ),
					popup_menu( {-name=> "choice",-values=>\@parishes, -default=>"$choice"} ),
					p(  submit( {-value=>"Get Data!"} ), ),
					end_form,
				), #td
				td( h1("OR"), ),
				td(
					font( {-face=>'Arial', -size=>'3'},"Please select a topic to view:"),
					start_form( {-action=>url,-name=>"by_topic"} ),
					popup_menu( {-name=> "topic",	-values=>\@topic_menu } ),
					p(  submit( {-value=>"Get Data!"} ), ),
					end_form,
				), #td
			  ), #Tr
			  Tr({-align=>'CENTER'},
			  	td({-colspan=>'3'},
			  		"Another Custom CGI Script by",br,
					a( {-href=>"http://www.nolaflash.com"}, img( {-src=>"/images/nola_logo.jpg",
					-alt=>"Got Funk?", -border=>'0'} ), ),a( {-name=>"#foot"} ),
				), #td
			  ),
			), #table

		end_html;
}

sub parishdata_x3 {
	Tr({-align=>"right"},
	td( {-align=>"center"}, b($r[$j++]), ),
	td( {-bgcolor=>"$data"}, @{$info{$choice}}[$i++] ),
	td( {-bgcolor=>"$data"}, @{$info{$choice}}[$i++] ),
	td( {-bgcolor=>"$data"}, @{$info{$choice}}[$i++] ),
	);
}

sub parishdata_x1 {
	Tr({-align=>"right"},
	td( {-align=>"center"}, b($r[$j++]), ),
	td( {-bgcolor=>"$data"}, "&nbsp;"),
	td( {-bgcolor=>"$data"}, "&nbsp;"),
	td( {-bgcolor=>"$data"}, @{$info{$choice}}[$i++] ),
	);
}