#!/usr/bin/perl

#use utf8;
use strict;

use Encode;

use CGI;
use JSON;

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/school.tmpl',
        #reqPermission   => 'eq_record_edit',
    }
);
    my $op = $input->{'op'};
    my $save;
    my $data_json    = $input->{'data'};
    my $data = from_json($data_json,{utf8=>1});

    if ($op && $op eq "remove"){
        $save = removeSchool($dbh,$data);
    }
    elsif ($op && $op eq "update"){
        $save = saveSchool($dbh,$data);
    }
    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_json => $schoolList_json
    );

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'}',
    zip             ='$s->{'zip'}',
    country         ='$s->{'country'}',
    phone           ='$s->{'phone'}',
    fax             ='$s->{'fax'}', 
    principalTitle  = '$s->{'principalTitle'}',
    principalName   = '$s->{'principalName'}',
    principalPhone  = '$s->{'principalPhone'}',
    principalEmail  = '$s->{'principalEmail'}',
    alternateTitle  = '$s->{'alternateTitle'}',
    alternateName   = '$s->{'alternateName'}',
    alternatePhone  = '$s->{'alternatePhone'}',
    alternateEmail  = '$s->{'alternateEmail'}'

where code='$s->{'code'}'
_SQL_
        return $dbh->do($sql);
    }
    else{
        $sql = <<_SQL_;
insert into eq_locationDirectory 
    (code,name,address,city,state,zip,country,phone,fax,principalTitle,principalName,principalPhone,principalEmail,alternateTitle,alternateName,alternatePhone,alternateEmail) 
values (
    "$s->{'code'}","$s->{'name'}","$s->{'address'}","$s->{'city'}","$s->{'state'}","$s->{'zip'}","$s->{'country'}","$s->{'phone'}","$s->{'fax'}",
     "$s->{'principalTitle'}","$s->{'principalName'}","$s->{'principalPhone'}","$s->{'principalEmail'}",
     "$s->{'alternateTitle'}","$s->{'alternateName'}","$s->{'alternatePhone'}","$s->{'alternateEmail'}");
_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,zip,phone,fax,principalName,principalPhone,principalEmail,alternateName,alternatePhone,alternateEmail 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 _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;
}