#!/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="Ki
+ds Count"></P></TD><TD ALIGN="CENTER"><P ALIGN="CENTER"><IMG SRC="/im
+ages/kidscount.gif" WIDTH=294 HEIGHT=59 BORDER=0 ALT="Kids Count!"></
+P></TD><TD ALIGN="RIGHT"><P ALIGN="RIGHT"><IMG SRC="/images/kidscount
+logo.gif" WIDTH=68 HEIGHT=100 BORDER=0 ALT="Kids Count"></P></TD></TR
+></TABLE></CENTER><P ALIGN="CENTER"><FONT SIZE="-1"><A HREF="http://w
+ww.agendaforchildren.org">« Home</A><br>If connection is slow, data i
+s mirrored <a href="http://www.nolaflash.com/cgi-bin/kidscount2.pl">h
+ere</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 pov
+erty","Number of children under age 18 in poverty","Median Family Inc
+ome(1998)","\% Unemployed (April 1999)","\# Unemployed (April 1999)",
+"FITAP (FY 1998)",
"Medicaid (March 1999)","Food Stamps (November 1998)","WIC (1998)","Ch
+ild Care Assistance (June 1998)","Child Care Assistance (June 1998)",
+"Live Births","\% Births To Teens","\% Low Birthweight Babies","\% Wo
+men Receiving<br>Adequate Prenatal Care","Infant Mortality Rate","\% 
+Up to Date Immunization<br>(Public Clinics 1998)","Children in custod
+y under supervision<br>of Office of Youth Development","Crimes agains
+t property","Crimes against persons","Abuse and neglect cases found v
+alid<br>(# of children)","Physical abuse","Neglect","Sexual abuse","C
+hildren in Foster Care","Registration","\$ Per Pupil Expenditure","\$
+ Average Teacher Salary","Special education services","Dropouts");

my @topic_menu = ("Total # Children","Children Under 20","Children Und
+er 5","\% Children in poverty","\# Children in poverty","Median Famil
+y Income","\% Unemployed","\# Unemployed","Receiving FITAP","Receivin
+g Medicaid","Receiving Food Stamps","Receiving WIC","Child Care Assis
+tance","CCA FINDWORK","Live Births","\% Births To Teens","\% Low Birt
+hweight 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 Registe
+red","\$ 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 Educa
+tion");

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 ti
+tles
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 Hea
+lth (1997)
            @toprow,
            parishdata_x3(), parishdata_x3(),parishdata_x3(), parishda
+ta_x3(),
            parishdata_x3(), parishdata_x1(),
        ), #table

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

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

        br, br, b($t[$k++]),br,
        table( {-width=>'600',-border=>'1'},  ##Public Education
            @toprow,
            parishdata_x3(), parishdata_x1(),parishdata_x1(), parishda
+ta_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","\% Bir
+ths 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","2
+0:40:3","Crimes against property",
    "21:43:1","Crimes against persons","22:44:1","Abuse and neglect ca
+ses","23:47:3","Physical abuse","24:50:3",
    "Neglect","25:53:3","Sexual abuse","26:56:3","Children in Foster C
+are","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"}, \@to
+prow ),
        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++] ),
    );
}
[download]