#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Constant;
use Date::Calc qw(
    Today 
    Add_Delta_Days 
);


use Opals::Date qw(
    date_text
    date_DHM_text
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);
use Opals::BarcodeMgmt qw(
    bcm_validateBc
);
use POSIX qw(
    ceil
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_setMsgValue
    loc_write
);
use Opals::BookCover qw (
    bookCover_syndetics
    bookCover_amazon
    bookCover_google
);
use Opals::Utility qw(util_getXmlRecord);
use JSON;

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

my $cgi         = CGI->new;
my $input       = $cgi->Vars();
my $syspref     = tmpl_preference($dbh);
my $validateBc  = $syspref->{'validateBarcode'};
my $barcodeType = $syspref->{'barcodeType'};
my $patronItemPrivacy=$syspref->{'patronItemPrivacy'};
my $anonymizeLoan=$syspref->{"anonymizeLoan"};
my $statsFrom=undef;
if(defined $anonymizeLoan && $anonymizeLoan>0){
    my ($year,$month,$day)=Today();
    ($year,$month,$day)=Add_Delta_Days($year,$month,$day,-1*$anonymizeLoan);
    $statsFrom=sprintf "%4d-%02d-%02d",$year,$month,$day;

}

my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/itemCircStatus.tmpl',
            reqPermission   => 'marc_edit|circ_loan|circ_return|circ_rsrv',
        }
);



my $bc  = $input->{'bc'};
$bc     =~ s/^\s+|\s+$//g;
$bc     = bcm_validateBc($dbh,$bc,$barcodeType) if($validateBc eq '1');


my $itemInfo = getItemInfo($dbh,$bc);
if(defined $itemInfo ){
    my $circStats=getCircStats($dbh,$bc,$statsFrom);
    my $loanList = $circStats->{'loanList'};
    my $itemTypeList = getItemTypeList($dbh);
    my $circStats=  {curBorrower=>getCurBorrower($dbh,$bc),
                     holdList=>getCurHoldList($dbh,$bc),
                     reserveList=>getCurReserveList($dbh,$bc),
                     totalLoan=>$circStats->{'totalLoan'},
                     loanStats=>$circStats->{'loanStats'},
                     totalReserve=>getTotalReserve($dbh,$bc)
                    };


    my $itemInfoJSON        =to_json($itemInfo,{pretty=>1});
    my $loanListJSON        =to_json($loanList,{pretty=>1});
    my $itemTypeListJSON    =to_json($itemTypeList,{pretty=>1});
    my $circStatsJSON       =to_json($circStats,{pretty=>1});



    $template->param(
                    itemInfo=>$itemInfoJSON,
                    loanList=>$loanListJSON,
                    itemTypeList=>$itemTypeListJSON,
                    circStats=>$circStatsJSON,
                    anonymizeLoan=>$anonymizeLoan,
                    loanStatsFrom=>$statsFrom);

}
    my $itemStatusMsgMap =loc_getMsgFile('circ/itemStatus.msg',{});
    loc_write($template,$itemStatusMsgMap);
 $template->param(hlpUrl     => Opals::Constant->getHlpUrl('circ_itemonloan') ,
                    barcode=>$bc,
                    patronItemPrivacy=>$patronItemPrivacy);

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

#============================================================================
sub  getCurBorrower{
    my($dbh,$bc)=@_; 

    my $sth = $dbh->prepare(<<_SQL_);
    select u.firstname,u.lastname ,u.userbarcode,l.dateLoan,l.dateDue
    from opl_loan l inner join opl_user u using(uid)
    where barcode=? && dateReturn is null
_SQL_

    $sth->execute($bc);
    my $user = $sth->fetchrow_hashref;
    $sth->finish;
    return $user;
}

#============================================================================
sub  getTotalLoan{
    my($dbh,$bc)=@_; 

    my $sth = $dbh->prepare("select count(*) as total from opl_loan where barcode=?");
    $sth->execute($bc);
    my $total = $sth->fetchrow_array;
    $sth->finish;
    return $total;
}
#============================================================================
sub  getTotalReserve{
    my($dbh,$bc)=@_; 
    my $sth = $dbh->prepare("select count(*) as total from opl_reserve inner join opl_item using (rid) where barcode=?");
    $sth->execute($bc);
    my($total) = $sth->fetchrow_array;
    return $total||0;

}
#============================================================================
sub  getCurReserveList{
    my($dbh,$bc)=@_; 
    my $sth = $dbh->prepare(<<_SQL_);
    select u.userbarcode,u.firstname,u.lastname ,r.dateReserve,r.dateExpiry,r.numCopyReserve
    from opl_user u inner join opl_reserve r on u.uid=r.uid inner join opl_item i on i.rid=r.rid
    where i.barcode=? && r.dateExpiry>now() && r.dateCancel is null && r.numCopyReserve>0
_SQL_
    
    $sth->execute($bc);
    my $rsList=[];
    while(my $user = $sth->fetchrow_hashref){
        push @$rsList,$user;
    }
    $sth->finish;
    return $rsList;

}
#============================================================================
sub  getCurHoldList{
    my($dbh,$bc)=@_; 
    my $sth = $dbh->prepare(<<_SQL_);
    select u.userbarcode,u.firstname,u.lastname, h.dateHold, h.dateExpiry
    from opl_user u inner join opl_reserve r on u.uid=r.uid 
         inner join opl_item i on i.rid=r.rid
         inner join opl_hold h using(idReserve)
    where i.barcode=? && h.dateExpiry>now() && h.dateCancel is null && idloan =0
_SQL_
    
    $sth->execute($bc);
    my $holdList=[];
    while(my $user = $sth->fetchrow_hashref){
        push @$holdList,$user;
    }
    $sth->finish;
    return $holdList;

}
#=======================================================
sub getBookCover{
    my($recList)=@_;
    my  $awsId =Opals::Context->preference('amazonId');
    my  $awsSecretKey =Opals::Context->preference('amazonSecreteKey');
    my  $syndeticsId =Opals::Context->preference('syndeticsId');
    if($syndeticsId ne''){
        bookCover_syndetics($recList);
    }
    elsif($awsId ne '' && $awsSecretKey ne ''){
        bookCover_amazon($recList,$awsId,$awsSecretKey);
    }
    else{
        bookCover_google($recList,'l');
    }

}

#============================================================================
sub  getItemInfo{
    my($dbh,$bc)=@_; 
    my $sql_bal=<<_SQL_;
    select if(c.balance>0,1,0) hasBalance,c.cid from opl_charge c inner join opl_odl o using(odl_id) 
           inner join opl_loan l using(idloan)  
    where l.barcode=? && o.type='lost' order by odl_id desc limit 1
_SQL_
    
    my $sql_info=<<_SQL_ ;  
        select  r.rid, r.title, r.author, r.pubName, r.pubDate,i.callNumber,i.location,
            if(s.status is not null,s.status,1) status,i.typeId,r.isbn
        from    opl_item i inner join opl_marcRecord r using(rid) left outer join opl_itemstatus s using(barcode)
        where   i.barcode = ? 
        order by s.id desc limit 1        
_SQL_

 my $sth = $dbh->prepare($sql_info);
   

    $sth->execute($bc) || return;
    my $rec = $sth->fetchrow_hashref;
    $sth->finish;
    if ($rec) {
        $rec->{'callNumber'}=undef if($rec->{'callNumber'} eq'');
        $rec->{"isbn"}=[{item=>$rec->{'isbn'}}];
        getBookCover($rec);
        my($hasBalance,$cid)=(0,0);
        if( $rec->{'status'}==3){#lost get charge balance
            ($hasBalance,$cid) =$dbh->selectrow_array($sql_bal, undef,$bc) ;
        }
        $rec->{"hasBalance"}=$hasBalance;
        $rec->{"cid"}=$cid;
        $rec->{'nonPubNotes'}=getNonPublicNotes($rec->{'rid'},$bc);
        $rec->{'circStatus'} = Opals::Circulation::circ_getRecCircStatus_book($dbh,$rec->{'rid'})
    }
    
    return $rec;
        
}
#============================================================================
sub getNonPublicNotes{
    my ($rid,$bc)=@_;
    my $retDat=[];
    my $xml=util_getXmlRecord($rid);
    if ($xml ne ''){
        my $marc = Opals::Marc::Record::newFromXml($xml);
        my @fields = $marc->field('852');
        foreach my $f852(@fields){
            if(defined $f852->subfield('p') && $f852->subfield('p') eq $bc ){
                if(defined $f852->subfield('x') ){
                    @$retDat=$f852->subfield('x');
                }
                last;
            }
       }
    }
    return $retDat;
}
#============================================================================
sub getCircStats{
    my($dbh,$bc,$statsFrom) = @_;
    my @borrowers = (); 
    my $sql = "select distinct l.idloan, dateLoan, dateDue, dateReturn, u.uid uid, firstname, lastname, 
                userbarcode, username , o.type    
    from opl_loan as l left outer join opl_user as u using(uid) 
         left outer join opl_odl o on l.idloan=o.idloan && o.type<>'overdue'  
    where l.barcode=?   order by dateLoan desc";
   
   my $sql_status = "select ondate,status from opl_itemstatus where barcode=? && ondate >? order by ondate  limit 1"; 
    
    my $query = $dbh->prepare($sql);
    $query->execute($bc);
    my $rec;
    my ($circStats)=$dbh->selectrow_array("select circStats from opl_item where barcode=?",undef,$bc);
    my $loanStats={};
    if($circStats ne''){
        $loanStats=decode_json($circStats);
    }

    while (my $rec = $query->fetchrow_hashref) {
        if(!$rec->{'uid'} ){
            $rec->{'uid'}         =-1;
            $rec->{'userbarcode'} ='No data';
            $rec->{'firstname'}   ='No data';
        }
        if($rec->{'type'} eq 'lost'){
            my ($ondate,$status)=$dbh->selectrow_array($sql_status,undef,$bc,$rec->{'dateReturn'});
            if(defined $status && $status ==1){
                $rec->{'returnLost'}=$ondate;
            }
        }
        if(!$rec->{'dateReturn'} ||  !defined $statsFrom  || $rec->{'dateLoan'} gt $statsFrom){
            push @borrowers, $rec; 
        }
=item        
        if(!defined $statsFrom ){
            my $year=substr($rec->{'dateLoan'},0,4);
            my $hasStats=0;
            while(my ($y,$c)= each %$loanStats){
                 if($year eq $y || $y =~ m/$y/){
                     $loanStats->{$y} = $c+1;
                     $hasStats=1;
                     last;
                 }
            }
            $loanStats->{$year} =1 if(!$hasStats);
        }
=cut        
    }
    $query->finish;
    my $totalLoan=0;
    my $loanStatsArr=[];
    foreach my $y(sort keys %$loanStats){
        push @$loanStatsArr,{year=>$y,loans=>$loanStats->{$y}};
        $totalLoan += $loanStats->{$y};
    }
    return {loanList=>\@borrowers,loanStats=>$loanStatsArr,totalLoan=>$totalLoan};
}
#============================================================================
sub getItemTypeList{
    my ($dbh) = @_;
    my $ret=[];
    my $sth= $dbh->prepare(<<_SQL_);
select  *
from    opl_itemType
where   itemCategory=1
_SQL_

   $sth->execute();
   while( my $t= $sth->fetchrow_hashref){
       push @$ret,$t;
   }
   $sth->finish;
   return $ret;

}


#============================================================================