#!/usr/bin/perl

#use utf8;
use strict;

use Encode;

use CGI;

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

use Opals::Constant;

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

my $cgi = CGI->new;
my $input = $cgi->Vars();

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'eqmnt/ajax/schoolInfo.tmpl',
        #reqPermission   => 'eq_record_edit',
    }
);
    my $op = $input->{'op'};
    my $save;
    while (my ($k,$v) = each %{$input}){
        $input->{$k} =  _escapeStr($v);

    }
    if ($op && $op eq "remove"){
        $save = removeSchool($dbh,$input);
    }
    else{
        $save = saveSchool($dbh,$input);
    }
    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'}};
        }
    }
    $template->param (
        save        => $save,
        schoolList  => $schoolList,
    );

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


sub saveSchool {
    
    my ($dbh,$s) = @_;
    my $ret = 1;
    my $sql="";
    if($dbh->do("select code from eq_locationDirectory where code = '$s->{'code'}'") ne "0E0" ){
        $sql = <<_SQL_ ;
update eq_locationDirectory 
    set name='$s->{'name'}',address='$s->{'address'}',city='$s->{'city'}',state='$s->{'state'}',
    country='$s->{'country'}',phone='$s->{'phone'}',fax='$s->{'fax'}'
where code='$s->{'code'}'
_SQL_
        return $dbh->do($sql);
    }
    else{
        $sql = <<_SQL_;
insert into eq_locationDirectory (code,name,address,city,state,country,phone,fax) values (
    "$s->{'code'}","$s->{'name'}","$s->{'address'}","$s->{'city'}","$s->{'state'}","$s->{'country'}","$s->{'phone'}","$s->{'fax'}");
_SQL_
        return $dbh->do($sql);
    }
}

sub removeSchool {

    my ($dbh,$s) = @_;
    if($dbh->do("select code from eq_locationDirectory where code = '$s->{'code'}'") ne "0E0" ){
        return $dbh->do("delete from eq_locationDirectory where code='$s->{'code'}'");
    }
    return 0;
}

sub _getSchoolList {
    
    my($dbh)=@_;
    my $sql = "select id,code,name,address,city,state,country,phone,fax 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 _escapeStr {
    my ($str) = @_;

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