#!/usr/bin/perl

use strict;
use CGI;
use Time::localtime;
use JSON;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }


my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'eqmnt/util/schoolList.tmpl',
    }
);

    my $schoolList = _getSchoolList($dbh);
    my $holdingPerSchoolList = _countHoldingPerSchool($dbh);
    my %mapSchoolHolding = map { $_->{'code'} => $_->{'count'}} @{$holdingPerSchoolList};
    foreach my $s(@{$schoolList}){
        $s->{'iCount'} = 0;
        if ($mapSchoolHolding{$s->{'code'}} &&  $mapSchoolHolding{$s->{'code'}} > 0){
            $s->{'iCount'} = $mapSchoolHolding{$s->{'code'}};
            $s->{'status'} = "";
        }
    }


    my $schoolList_json = to_json($schoolList,{pretty=>1});


$template->param(
    
    schoolList      => $schoolList,
    schoolList_json => $schoolList_json
    
);


tmpl_write($dbh, $cgi, $cookie, $template);

#================================================================================
sub _getSchoolList {
    
    my($dbh)=@_;
    #my $sql = "select id,code,name,address,city,state,country,phone,fax from eq_locationDirectory order by name";
    my $sql = "select * from eq_locationDirectory order by name";
    my $schoolList = $dbh->selectall_arrayref($sql,{Slice=>{}} ,());
    return $schoolList;

}

sub _countHoldingPerSchool {
    
    my ($dbh)=@_;
    my $sql = "select f.sfValue as code,count(*) as count from eq_records r inner join eq_items i using(rid) inner join eq_itemFields f using(rid,iid) where r.deleted='0' && f.sfId=18 && i.available='1' group by sfValue";
    my $holdingPerSchoolList = $dbh->selectall_arrayref($sql,{Slice=>{}},());
    return $holdingPerSchoolList;

}

sub _escapeXml {
    my ($str) = @_;

    return unless $str;
    
    $str =~ s/&/&/g;
    $str =~ s/"/"/g;
    $str =~ s/'/'/g;
    $str =~ s/</&lt;/g;
    $str =~ s/>/&gt;/g;
    return $str;
}